Hologres:EXTERNAL_FILES Function

Version requirement

Your Hologres instance must run version V4.1 or later. If your instance runs an earlier version, upgrade it. For more information, see Instance upgrades.

Permission configuration

Only RAM users or Alibaba Cloud accounts can use EXTERNAL_FILES. Custom accounts are not supported. Before using EXTERNAL_FILES, complete the following steps. EXTERNAL_FILES accesses OSS through a RAM role. First, create a RAM role and grant it to the Hologres service. Then, grant OSS permissions to that role.

Create a role

• Log on to the RAM console at RAM Roles. Go to the Roles page and click Create Role.

• In the dialog box, set Trusted Entity Type to Cloud Service and Trusted Entity to Real-time Data Warehouse Hologres.

• Enter a role name and complete the creation.

Configure OSS access permissions

Grant OSS permissions to the RAM role:

• Read data: Grant the AliyunOSSReadOnlyAccess permission.

• Write data: Grant the AliyunOSSFullAccess permission.

Grant the GrantAssumeRole permission to the SQL user

If the user who runs the SQL is not an Alibaba Cloud account, first grant them the GrantAssumeRole permission:

• In the RAM console, create a permission policy. Go to Policies > Create Policy > Script Editor. Paste the following JSON. Replace Role_ARN with the same role_arn used in EXTERNAL_FILES. To find the Role ARN, see FAQ about RAM roles and STS tokens.

{
  "Version": "1",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "hologram:GrantAssumeRole",
      "Resource": "<RoleARN>"
    }
  ]
}

Then attach this policy to the user who runs the query. For a RAM user, go to Users > Permissions > Add Permissions. For a RAM role, go to Roles > Permissions > Add Permissions.

Common parameters

Read parameters

Write parameters

Example 1: Query a CSV file

Query a CSV file in OSS and infer the schema automatically:

SELECT * FROM EXTERNAL_FILES(
    path = 'oss://mybucket/data/',
    format = 'csv',
    csv_skip_header = 'true',
    csv_delimiter = ',',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role'
);

Query a CSV file and specify the schema manually:

SELECT * FROM EXTERNAL_FILES(
    path = 'oss://mybucket/data/',
    format = 'csv',
    csv_skip_header = 'true',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role'
) AS (id int, name text, amount decimal(10,2));

Example 2: Query a Parquet file

SELECT * FROM EXTERNAL_FILES(
    path = 'oss://mybucket/parquet_data/',
    format = 'parquet',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role'
);

Example 3: Query an ORC file

SELECT * FROM EXTERNAL_FILES(
    path = 'oss://mybucket/orc_data/',
    format = 'orc',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role'
);

Example 4: Import data into a Hologres table

-- Create the target table
CREATE TABLE orders (
    order_id int,
    customer_name text,
    amount decimal(10,2),
    PRIMARY KEY(order_id)
);

-- Import data from OSS
INSERT INTO orders 
SELECT * FROM EXTERNAL_FILES(
    path = 'oss://mybucket/orders/',
    format = 'csv',
    csv_skip_header = 'true',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role'
) AS (order_id int, customer_name text, amount decimal(10,2));

Example 5: Export data to OSS

Export data to multiple files:

INSERT INTO EXTERNAL_FILES(
    path = 'oss://mybucket/export/',
    format = 'csv',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role',
    target_file_size_mb = '100'
) SELECT * FROM orders;

Export data to a single file:

INSERT INTO EXTERNAL_FILES(
    path = 'oss://mybucket/export/',
    format = 'csv',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role',
    single_file = 'true'
) SELECT * FROM orders;

Example 6: Use a serverless resource group

SET hg_computing_resource = 'serverless';

SELECT * FROM EXTERNAL_FILES(
    path = 'oss://mybucket/data/',
    format = 'csv',
    oss_endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::123456789:role/hologres-role'
);

Automatic schema inference

If you do not specify a schema using the AS clause, Hologres infers the schema automatically:

• Parquet/ORC: Infer from file metadata.

• CSV with headers: Infer from the first row (header) and data content.

• CSV without headers: All files must have identical schemas.

Hologres scans the number of files specified by schema_deduce_file_num and takes the union of all schemas.

Schema inference behavior

Type mapping

When EXTERNAL_FILES reads ORC or Parquet files, it maps file types to PostgreSQL types. The mappings are listed below. Unsupported types are noted under each table.

ORC type mapping

Parquet type mapping