This is an automated email from the ASF dual-hosted git repository. dzamo pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/drill-site.git
commit 3aaa82d102ca9d9bd6022b6d4a63d30957be4900 Author: James Turton <[email protected]> AuthorDate: Tue Feb 21 15:10:12 2023 +0200 Document the MS Access storage plugin. --- .../plugins/180-ms-access-plugin.md | 47 ++++++++++++++++++++++ 1 file changed, 47 insertions(+) diff --git a/_docs/en/connect-a-data-source/plugins/180-ms-access-plugin.md b/_docs/en/connect-a-data-source/plugins/180-ms-access-plugin.md new file mode 100644 index 000000000..748420b3c --- /dev/null +++ b/_docs/en/connect-a-data-source/plugins/180-ms-access-plugin.md @@ -0,0 +1,47 @@ +--- +title: "MS Access Storage Plugin" +slug: "MS Access Storage Plugin" +parent: "Connect a Data Source" +--- + +**Introduced in release:** 1.21 + +This plugin enables Drill to read Microsoft Access database files. This plugin can read Access Files from all versions later than Access 1997. + +## Configuration +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. + +```json +"msaccess": { + "type": "msaccess", + "extensions": ["mdb", "accdb"] +} +``` + +## Schemas +Drill will discover the schema automatically from the Access file. The plugin does support schema provisioning for consistency, but is not recommended. + +## Querying a Table +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. + +```sql +SELECT * +FROM table(dfs.`file_name.accdb` (type=> 'msaccess', tableName => 'Table1')) +``` + +## Metadata Queries +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: + +```sql +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"] | ++--------+-------------------------+-------------------------+-----------+-----------+----------------------------------------------------------------------+ + +``` +## Password-Protected Files +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.
