This plugin enables Drill to read Microsoft Access database files. This plugin can read Access Files from all versions later than Access 1997.
Simply add the following to any Drill file system configuration. Typically, MS Access files will use the extension accdb or mdb. Drill comes pre-configured to recognize these extensions as MS Access.
"msaccess": { "type": "msaccess", "extensions": ["mdb", "accdb"] }
An Access file can define tables that live in another database file, referenced by an absolute local path or a UNC share embedded in the file itself. Drill refuses to follow those references by default: reading an untrusted file would otherwise make the Drillbit open a path of the file author's choosing, as the Drillbit service account.
Note: Before Drill 1.23, linked tables were followed automatically. Queries against a linked table now fail with a permission error unless an administrator enables them. Listing the tables in a file (a query without tableName) still works: linked tables are shown with null dates and counts.
If you trust every file that can be queried and need linked tables to work, an administrator can enable them by setting the following boot option in drill-override.conf on every Drillbit:
drill.exec.storage.msaccess.allow_linked_databases: true
This is deliberately not a format config or table-function option, so that a query user cannot turn it on.
Drill will discover the schema automatically from the Access file. The plugin does support schema provisioning for consistency, but is not recommended.
Access files will contain multiple tables. To access a specific table, use the table() function in the FROM clause, and specify the table name using the tableName parameter, as shown below.
SELECT * FROM table(dfs.`file_name.accdb` (type=> 'msaccess', tableName => 'Table1'))
Since an Access file may contain multiple tables, there needs to be a way to determine what tables are present in the Access file. In Drill, simply querying a file, without specifying a tableName will result in a metadata query, rather than getting the actual data back.
For example:
SELECT * FROM dfs.test.`access/data/V2019/extDateTestV2019.accdb`; +--------+-------------------------+-------------------------+-----------+-----------+----------------------------------------------------------------------+ | table | created_date | updated_date | row_count | col_count | columns | +--------+-------------------------+-------------------------+-----------+-----------+----------------------------------------------------------------------+ | Table1 | 2021-06-03 20:09:56.993 | 2021-06-03 20:09:56.993 | 9 | 6 | ["ID","Field1","DateExt","DateNormal","DateExtStr","DateNormalCalc"] | +--------+-------------------------+-------------------------+-----------+-----------+----------------------------------------------------------------------+
The password protection in Access is just a software level protection and really does not offer any security. Drill can query password protected files without any password.