When source data resides in object storage external to VantageCloud Lake, one data loading approach is to use a foreign table to do an INSERT-SELECT into your target table. The steps involved are as follows:
- Set up data access privileges. For details, see Use Native Object Store to Work with Data in External Object Storage.
- Provide security credentials to read the source files, if required. For details, see Use Native Object Store to Work with Data in External Object Storage.
- Define the layout of the source file by following the steps in CREATE FOREIGN TABLE.
- Issue an INSERT-SELECT statement to load the data from the source files to the target table in the Object File System. For details, see INSERT-SELECT Statement.
The following are examples of setting up security credentials and a foreign table to read the source files:
- Setting up security credentials to read the source files.
Example: Set up an authorization object.
CREATE AUTHORIZATION MyAuthObject USER 'access_key_ID' PASSWORD 'access_secret_key';
- Setting up a foreign table to read the source files.
Example: Set up a foreign table.
CREATE FOREIGN TABLE ForeignTableName USING ( LOCATION('/connector/external_file_path/) );
The following are examples of loading data in different formats:
Example: CSV and Parquet
INSERT INTO MyLocalTableName SELECT Column1, Column2, ColumnN FROM ForeignTableName;
Example: JSON
INSERT INTO MyLocalTableName SELECT payload.Column1, payload.Column2,payload.ColumnN FROM ForeignTableName