To set up an Analytics Database to Delta Lake integration using manifest files and query Delta tables:
- See Delta Lake Documentation for instructions on how to generate and configure manifests of a Delta table.
- Define a new foreign table in Analytics Database using the format SymlinkTextInputFormat and the manifest location path-to-delta-table/_symlink_format_manifest/:
CREATE FOREIGN TABLE mytable , EXTERNAL SECURITY myauthobj USING ( LOCATION('/s3/mybucket.s3.amazonaws.com/path-to-delta=table/_symlink_format_manifest/') MANIFEST('TRUE') TABLE_FORMAT('DELTALAKE') );
The presence of the _symlink_format_manifest keystring confirms that the table is a Delta Lake table and is validated only if the MANIFEST parameter is set to TRUE and the TABLE_FORMAT parameter is set as DELTALAKE.SymlinkTextInputFormat instructs Analytics Database to read the data for mytable by reading the manifest file instead of using a directory listing to find data files. Replace mytable with the name of the external table and path-to-delta-table with the absolute path to the Delta table.Even though Delta Lake supports schema evolution and queries on a Delta table automatically use the latest schema regardless of the schema defined in the table, Vantage uses the schema defined in its foreign table definition and does not query with the updated schema until the table definition is updated to the new schema.
Generate Manifests of a Delta Table
Configure Analytics Database to Read the Generated Manifests