Hologres:EXTERNAL_FILES

Permission configuration

EXTERNAL_FILES accesses OSS through a RAM role. Complete the following steps before running EXTERNAL_FILES.

Step 1: Create a RAM role

1. Log on to the RAM console and go to RAM Roles.

2. Click Create Role.

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

4. Enter a role name and complete the creation.

Step 2: Grant OSS permissions to the role

Attach an OSS policy to the RAM role based on the operations you need:

• Read data: Attach AliyunOSSReadOnlyAccess.

• Write data: Attach AliyunOSSFullAccess.

Step 3: Grant the GrantAssumeRole permission to RAM users

Skip this step if the user running the SQL is an Alibaba Cloud account. For RAM users, grant them the GrantAssumeRole permission as follows:

1. In the RAM console, go to Policies and click Create Policy.

2. Switch to Script Editor and paste the following JSON. Replace <RoleARN> with the ARN of the role you created. To find the ARN, see FAQ about RAM roles and STS tokens.

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

3. Attach this policy to the user:

   • For a RAM user: go to Users > Permissions > Add Permissions.

   • For a RAM role: go to Roles > Permissions > Add Permissions.

Common parameters

Wildcards supported in `path`

Read parameters

Write parameters

Query a CSV file

Query a CSV file with automatic schema inference:

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 with a manually specified schema:

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));

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'
);

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'
);

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));

Export data to OSS

Export to multiple files with a size limit per 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',
    target_file_size_mb = '100'
) SELECT * FROM orders;

Export all 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;

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 inference

If you omit the AS clause, Hologres infers the schema automatically:

• Parquet and ORC: Schema is inferred from file metadata.

• CSV with a header row: Schema is inferred from the header and data content. Set csv_skip_header = 'true' so the header row is not read as data.

• CSV without a header row: All files in the path must have identical column structures.

Hologres scans up to schema_deduce_file_num files (default: 5) and takes the union of all discovered schemas.

Schema mismatch behavior

When the inferred or specified schema does not exactly match the file contents, Hologres handles mismatches as follows:

Example: querying files with mismatched columns

Suppose you have two CSV files in the same path:

orders_jan.csv — 3 columns: order_id, customer_name, amount

orders_feb.csv — 4 columns: order_id, customer_name, amount, region

When you query the path without specifying a schema, Hologres takes the union of both schemas. Rows from orders_jan.csv return NULL in the region column:

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'
);

To avoid unexpected NULLs, specify the schema explicitly using the AS clause, or ensure all files share the same column structure.

Type mapping

ORC type mapping

ORC types UNION, STRUCT, and MAP are not supported.

Parquet type mapping

Parquet types STRUCT and MAP are not supported.