createExternalTable

As the data sources become increasingly diverse, users require a unified platform to conveniently access and query data across multiple databases and file systems. DolphinDB provides the external table feature that allows external data to be used like local tables, supporting common queries and partial predicate pushdown, thereby improving usability and query efficiency.

Currently supports mainstream data sources such as Oracle, MySQL, SQL Server, S3, and Parquet. Users only need to install the corresponding connection plugins (e.g., ODBC, AWS, Parquet) to use this feature.

First introduced in version: 3.00.4

Create External Tables

Syntax

createExternalTable(tableName, externalType, config, [columnNames], [columnTypes])

Details

Creates an external table and returns its corresponding table object. Then you can query and process the external data source with DolphinDB scripts just like a local table.

Identify the external table type via externalType, which can be 'oracle', 's3', 'parquet', 'mysql', 'sqlserver', or 'dolphindb', 'hive', 'gauss', 'clickhouse', or 'sqlite'. Configuration parameters differ depending on the external data source.

For Oracle, MySQL and SQL Server, configure connectionString, e.g., Dsn=MyOracleDB;Uid=user;Pwd=pass. Refer to ODBC.
For S3:


config Dictionary Key	config Dictionary Value
filePath	a string indicating the file path in S3 bucket. Currently support files with the extensions .csv or .csv.gz.
region	a string indicating the AWS region name (e.g., "us-east-1")
bucket	a string indicating the S3 bucket name
accessKeyId	a string indicating the AWS access key ID
secretAccessKey	a string indicating the AWS access key

If the data is in Parquet format, configure the following keys:

Note:

Starting with version 3.00.6, you can read multiple files and build Hive partitions when creating a Parquet external table.

When multiple files are used, all read Parquet files must have the same schema.
If "hivePartition" is set to true, the system builds partitions only from the files actually matched by fileName; files on disk that belong to the same partition but are not specified by fileName are not returned by queries.


config Dictionary Key	config Dictionary Value
fileName	A STRING scalar or vector that specifies the path name of one or more Parquet files. For HDFS systems, this is the path name on HDFS. You can use wildcards in paths to match multiple files, but the path must point to specific Parquet files and cannot be a directory path. For example: 'path/to/a.parquet', 'path/to/some/.parquet', ['path/to/a.parquet', 'path/to/some/.parquet']. Paths such as 'path/to/folder/' or 'path/to/folder' are not supported. Wildcard matching must return at least one file. Recursive wildcard matching with ** is not supported. Wildcards are not supported for matching HDFS paths.
hivePartition	Optional. A BOOL value. The default is false, in which case Hive partitions are not built. When set to true, the system builds Hive partitions from the Parquet file paths specified by fileName, and partition pruning is applied based on the WHERE clause in a query.Hive partitions require a directory structure of /partition_col=value/; otherwise, table creation fails. For example: `/warehouse/events/ dt=2026-04-24/ part-000.parquet dt=2026-04-25/ part-000.parquet` In the example above, dt is the partition column, and dt=2026-04-24 is a partition directory. The partition column does not need to exist in the Parquet file. If it does not exist, the partition column is appended to the end of the external table after creation. During partition pruning, the system decides which files to read only from the directory name (for example, dt=2026-04-24). It does not verify whether the actual data in the Parquet file matches the directory name. If the dt=2026-04-24 directory contains files from the dt=2026-04-25 directory and the files include the partition column, query results will be incorrect.
fileName	a string indicating the file path name of parquet. If it is an HDFS system, it indicates its path name on HDFS.
nameNode	(optional) a string indicating the IP address where the HDFS is located
port	(optional) an integer indicating the port number of HDFS
username	(optional) a string indicating the username for login
kerbTicketCachePath	(optional) a string indicating the Kerberos path used to connect to HDFS
keytabPath	(optional) a string indicating the path of the keytab files used to authenticate the obtained Kerberos tickets
principal	(optional) a string indicating the Kerberos principals to be authenticated
lifetime	(optional) a string indicating the lifetime of the generated tickets

For remote DolphinDB nodes:


config Dictionary Key	config Dictionary Value
siteAlias	the alias of the remote node. It needs to be defined in configuration. Once configured, host and port do not need to be specified.
host	a string indicating the host name (IP address or website) of the remote node. Once configured, siteAlias does not need to be specified.
port	an integer indicating the port number of the remote node
userId	(optional) a string indicating the username
password	(optional) a string indicating the user’s password
enableSSL	(optional) a boolean value determining whether to use the SSL protocol for encrypted communication. The default value is false.
database	(optional) a string indicating the name of remote database to be read. If unspecified, tableName is a shared table.

Install and load the corresponding plugins based on external data sources, for example:

Oracle / MySQL / SQL Serverr / Hive / GaussDB / ClickHouse / SQLite: odbc.
S3: aws.
To read Parquet files, load the parquet and hdfs plugins (if the files are stored in HDFS).

Parameters

tableName is a STRING scalar indicating the external table name.

externalType is a STRING scalar indicating data source type, which can be 'oracle', ‘s3’, 'parquet', 'mysql', 'sqlserver', 'dolphindb', 'hive', 'gauss', 'clickhouse', or 'sqlite'.

config is a dictionary for setting connection parameters. Configuration parameters differ depending on the external data source. Refer to Details.

columnNames (optional) is a STRING vector for specifying the column names. If specified, names must be provided for all columns; if not, defaults to the external tables' original column names.

columnTypes (optional) is equal in length to columnNames for specifying the column types. If not specified, the system automatically performs the conversion based on the external table type.

Examples

1. Create an external table that maps to the aka_name table in Oracle. Then you can query its data in DolphinDB.

loadPlugin("odbc")
oracle_cfg = dict(["connectionString"], ["Dsn=MyOracleDB"])
t = createExternalTable("aka_name", "oracle", oracle_cfg) 

select t.name from t where t.id > 200 limit 50

2. Create an external table that maps to the source data file in S3.

loadPlugin("aws")
aws_cfg = dict(["filePath", "region", "bucket", "accessId", "secretKey"], ["demo.csv.gz", 'cn-north-1', 'tests3func', 'AKIAXTOOQXTLU44HF75E', 
'S0OzvoQnlmFRSn1RzhK5b0yY4IpbrvYTyF5bwtyw'])
t = createExternalTable("test","s3", aws_cfg,`Open`High`OpenInt, [DOUBLE, DOUBLE, INT]);
select count(*) from t where Open > 0.5 and High < 0.6 limit 1;

3. Create an external table that maps the distributed table pt in the remote DolphinDB node.

ddb_cfg = dict(["host", "port", "userId", "password", "database"], ["192.168.0.130", 8848, "admin", "123456", "dfs://textDB"])
t = createExternalTable("pt", "dolphindb", ddb_cfg2)
select min(vol) from t where qty > 1500

Example 4. Create an external table from multiple Parquet files and build Hive partitions.

loadPlugin("parquet")

parquet_cfg = dict(
    ["fileName", "hivePartition"],
    ["/home/data/bar/Identity=*/Year=202*/*.parquet", true]
)

t = createExternalTable("part", "parquet", parquet_cfg)

select * from t where Identity = "CFFEX.IC" and Year > 2025 limit 1;

In the example above, Identity and Year are partition columns. When the WHERE clause includes partition columns that can be used for partition pruning, the system first uses partition information to filter the files to read, reducing scan volume and improving query performance. In addition, when LIMIT in a query can be pushed down—that is, when the first N rows can be determined without reading the full dataset—the system stops scanning early once it has retrieved the required amount of data.

Related Function: runExternalQuery