SQL External Table Functions

SQL External Table Functions are built-in table functions used to read data from a source external to the database. The table functions are used in SQL statements where external data is used in-place of a regular SQL table or subquery. The location of the data files can be on the local file system, or remote, such as Amazon S3.

The result set of a table function is a temporary table that exists during SQL statement execution. This table can be queried further, joined with other tables, or used in any other way that a regular table can be used.

External Data Source

The following file formats are supported:

  • CSV

  • Parquet

If the source data is not formatted correctly, the data may not be imported as expected, or may fail completely.

Local File Access Configuration

An external data directory must be configured before accessing local files. Once configured, only files located within this directory may be read using the READ_CSV and READ_PARQUET table functions. The external data directory can be configured using the external-data-dir database option. The path must be an absolute path to a directory on the local file system. An external data directory can be configured for each TE.

Remote File Access Configuration

Remote files can be read directly via HTTPS or Amazon S3. Accessing files via HTTPS does not require any configuration. Accessing files located on Amazon S3 requires configuration of credentials.

The following S3 credentials are required:

  • S3_ACCESS_KEY_ID

  • S3_REGION

  • S3_SECRET_ACCESS_KEY

Use SET to configure the Access Key ID, Region, and Secret Access Key. For more information, see SET.

Amazon S3 credentials can be configured for each connection. The pseudo table SYSTEM.CONNECTIONPROPERTIES can be used to check if credentials are configured.

READ_CSV

READ_CSV returns the data in the specified CSV file. It takes only one parameter, the relative or absolute path to the CSV file. The path can be local or remote. The path to the file can be relative to external-data-dir, or absolute. If path is absolute, it must be located within the external-data-dir.

The CSV source file must be valid UTF-8.

The data in the CSV file must be of type BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP, or VARCHAR. Any other data type will be interpreted as VARCHAR.

Syntax

READ_CSV('path/to/the/file.csv')
READ_CSV('s3://path/to/the/file.csv')
READ_CSV('https://path/to/the/file.csv')

Example

SELECT
    ID, NAME, AGE, COUNTRY
FROM
    READ_CSV('people.csv')
WHERE
    ID < 6;
 ID   NAME   AGE   COUNTRY
 --- ------- ---- ---------

  1  Alice    25  USA
  2  Bob      30  Canada
  3  Charlie  35  UK
  4  Diana    40  Germany
  5  Evan     28  France

READ_PARQUET

READ_PARQUET returns the data in the specified parquet file. It takes only one parameter, the relative or absolute path to the parquet file. The path can be local or remote. The path to the file can be relative to external-data-dir, or absolute. If path is absolute, it must be located within the external-data-dir.

Syntax

READ_PARQUET('path/to/the/parquet/file.parquet')
READ_PARQUET('s3://path/to/the/file.parquet')
READ_PARQUET('https://path/to/the/file.parquet')

Example

SELECT
    PRICE, PARKING, FURNISHINGSTATUS
FROM
    READ_PARQUET('docs/test/houseprices.parquet')
WHERE
    AREA > 12000
ORDER BY PRICE;
  PRICE   PARKING  FURNISHINGSTATUS
 -------- -------- -----------------

  3500000    0      unfurnished
  4900000    2      furnished
  5943000    2      semi-furnished
  6790000    2      furnished
  6930000    1      furnished
  9800000    2      furnished
 10150000    0      unfurnished

COPY…​TO

COPY…​TO exports data from a table or the results of a SQL query to a Parquet or CSV file. The following parameters are required:

  • a table name or a SQL query

  • the relative or absolute path to the file or directory. The path can be local or remote. The path to the local file can be relative to external-data-dir, or absolute. If path is absolute, it must be located within the external-data-dir.

  • Format of the file as Parquet or CSV

A scratch directory must be configured using the scratch-dir database option before exporting files. This is used to store temporary data while exporting.

The following parameters are optional:

  • PARTITION_BY, used to export data into a directory structure based on one or more column values. If PARTITION_BY is used, destination will be a directory. It will contain sub directories based on the partitioning columns.

  • APPEND, used to add unique files to the existing destination directory structure.

  • WRITE_PARTITION_COLUMNS, used to specify whether partitioning columns are written to the output file. If TRUE, the partitioning columns will be written to file. If FALSE, the partitioning column data will be used to only to create directory structure.

  • RETURN_STATS, used to return metrics about the COPY…​TO execution.

Metrics Description

PEAKMEMUSAGE

The peak amount of memory allocated during the execution of the statement

QUERYEXECTIME

The time taken to execute the (table or SELECT) query in microseconds

DATALOADTIME

The time taken to prepare the data for export in microseconds

DATAEXPORTTIME

The time taken to export the prepared data to file in microseconds

TOTALROWCOUNT

The total number of rows exported to all files (PARQUET only)

TOTALFILESIZE

The total size of all files created in bytes (PARQUET only)

FILECOUNT

The number of new files created (PARQUET only)
Only access external files from trusted sources. NuoDB does not validate or perform security checks on externally sourced files.

Syntax

COPY { [schema.]table_name | (query) }
TO 'destination' ( FORMAT { PARQUET | CSV } [, option ...] );

Where option can be the following:

PARTITION_BY ( column_name [, column_name ...] )
APPEND { TRUE | FALSE }
WRITE_PARTITION_COLUMNS { TRUE | FALSE }
RETURN_STATS

Example

CREATE TABLE orders (id INT, item VARCHAR(255), order_date DATE);
INSERT INTO orders VALUES (1, 'pen', '2025-03-02');
INSERT INTO orders VALUES (2, 'book', '2025-03-02');
INSERT INTO orders VALUES (3, 'stapler', '2026-04-12');
INSERT INTO orders VALUES (4, 'paper', '2026-04-22');
INSERT INTO orders VALUES (5, 'pencil', '2026-06-10');
COPY orders TO 'all_orders.csv' (FORMAT CSV);

SELECT * FROM READ_CSV ('all_orders.csv');
ID   ITEM   ORDER_DATE
 --- ------- -----------
  1  pen     2025-03-02
  2  book    2025-03-02
  3  stapler 2026-04-12
  4  paper   2026-04-22
  5  pencil  2026-06-10
COPY (
    SELECT id, item
    FROM orders
    WHERE id > 2
)
TO 'some_orders.parquet' (
    FORMAT PARQUET
);
SELECT *
FROM READ_PARQUET('some_orders.parquet');
 ID   ITEM
 --- -------
  3  stapler
  4  paper
  5  pencil
COPY (
    SELECT id, item, order_date, year(order_date) as year, month(order_date) as month
    FROM orders
    WHERE order_date < '2026-01-01'
)
TO 'orders' (
    FORMAT PARQUET,
    PARTITION_BY ( year, month)
);
SELECT *
FROM READ_PARQUET ('orders/*/*/*');
ID  ITEM  ORDER_DATE  MONTH  YEAR
--- ----- ----------- ------ -----
1  pen   2025-03-02    3    2025
2  book  2025-03-02    3    2025
COPY (
    SELECT id, item, order_date, year(order_date) as year, month(order_date) as month
    FROM orders
    WHERE order_date >= '2026-01-01'
)
TO 'orders' (
    FORMAT PARQUET,
    PARTITION_BY (year, month),
    APPEND TRUE
);
SELECT id, item, order_date
FROM READ_PARQUET ('orders/*/*/*');
ID   ITEM   ORDER_DATE
--- ------- -----------
1  pen     2025-03-02
2  book    2025-03-02
3  stapler 2026-04-12
4  paper   2026-04-22
5  pencil  2026-06-10