Command Reference
Data Wrapper
CREATE SERVER
Create new server objects.
Syntax
Description
A foreign server object represents a named external datasource, along with configuration information required to access the datasource. Server objects are referenced during foreign table creation in order to indicate the backing datasource for the created table.
Parameters
Foreign Server Options for File Data Wrappers (delimited_file, parquet_file, and regex_parsed_file)
Foreign Server Options for ODBC Data Wrapper
ODBC HeavyConnect is currently in beta.
Examples
Create a local server that uses the delimited_file
data wrapper and the file path /home/my_user/data/csv/
:
Create an AWS Server that uses the delimited_file
data wrapper with an AWS S3 bucket "fsi-s3-bucket" in the us-west-1 AWS region:
Create an ODBC server using a data source name (DSN):
Create an ODBC server using a connection string:
To prevent collisions with available HeavyDB default servers, server names with default*, system*, and internal* (case insensitive) prefixes are not allowed.
ALTER SERVER
Update server objects.
Syntax
Description
Allow users to update properties of existing server objects.
Parameters
Example: Update server property
Update the my_csv_server object's base path “/home/my_user/data/csv/” to “/home/my_user/data/new_csv/”:
Example: Change server name
Change the my_csv_server object's name to my_new_csv_server:
DROP SERVER
Delete an existing server object
Syntax
Parameters
Example
Delete the my_csv_server server object:
SHOW SERVERS
View information about existing server objects.
Syntax
Column Results
Example - View all server objects in the current database
Example - View all server objects with the delimited_file data wrapper type
Example - View all server objects with the name containing “csv”
CREATE FOREIGN TABLE
Creates the foreign table.
Syntax
Description
A foreign table acts as a proxy to an external data source for which access details are provided by a server object. A foreign table provides an interface for querying data from an external datasource in a similar way to a local table.
Parameters
Foreign Tables Refresh Options
Foreign Table Options for File Data Wrappers (delimited_file, parquet_file, and regex_parsed_file)
Additional Foreign Table Options for the delimited_file Data Wrapper
All delimited file parser options mentioned in https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#csv-tsv-import
Additional Foreign Table Options for the regex_parsed_file Data Wrapper
Foreign Table Options for ODBC Data Wrapper
ODBC HeavyConnect is currently in beta.
Example - Using the default local file server and default property
Create a foreign table that accesses a local CSV file at /home/my_user/data/csv/my_file.csv using this default server object.
Example 2 - Using a custom local file server
Create a foreign table that accesses a local CSV file at path /home/my_user/data/csv/my_other_file.csv using the above my_csv_server object.
The default "default_local_delimited" server object can also be used to accomplish the above. However, if you use a custom server object, you do not have to specify the full path when creating foreign tables.
Example 3 - Using the custom server and duration property
Create a foreign table that accesses a remote Parquet file named my_parquet_file, referenced by my_parquet_server object, for which content could change every 2 days.
Example 4 - Using S3, custom S3 server, and the S3_Select option
Use the CREATE USER MAPPING command to set access credentials when using S3. S3_Select requires set credentials even when using public buckets.
ALTER FOREIGN TABLE
Update properties of existing foreign tables.
Syntax
The following table options can be altered:
ALLOW_FILE_ROLL_OFF
BUFFER_SIZE
REFRESH_INTERVAL
REFRESH_START_DATE_TIME
REFRESH_TIMING_TYPE
REFRESH_UPDATE_TYPE
SQL_ORDER_BY
SQL_SELECT
Description
Allow users to update properties of existing foreign tables.
Parameters
Example - Change foreign table name
Example - Change foreign table column name
Example - Change the foreign table update type
Example - Change the foreign table update schedule
DROP FOREIGN TABLE
Delete a foreign table.
Syntax
Description
Allows users to delete an existing foreign table.
Parameters
Example: Delete my_csv_table
REFRESH FOREIGN TABLES
Refresh a foreign table.
Syntax
Description
Allows users to do an on-demand refresh of one or more foreign tables.
Options
Parameters
User Mappings
A user mapping is an object that contains access credentials to an external datasource/foreign server. User mappings provide secure handling of user credentials when access to restricted external datasources or foreign servers is required. User mapping DDL commands can only be executed by super users or owners of referenced foreign servers. The following DDL commands are supported.
CREATE USER MAPPING
Create new user mappings.
Syntax
Description
Allows users to create new user mappings. See related AWS documentation for information about AWS credentials.
Parameters
S3 User Mapping Options
AWS credentials that are configured on the server, such as IAM roles, can be used in place of user mappings. Enable this option with the allow-s3-server-privileges
server configuration.
ODBC User Mapping Options
ODBC HeavyConnect is currently in beta.
Examples
Set up credentials for users to be able to query a foreign table (see related documentation for more details) that gets data from a previously created AWS S3 backed my_parquet_server,
DELETE USER MAPPING
Delete existing user mappings.
Syntax
Description
Allows users to delete existing user mappings.
Parameters
Examples
Delete user mapping that was setup for my_parquet_server.
Last updated