Hitachi

Hitachi Advanced Database SQL Reference

3.6.1 Specification format and rules for the CREATE FOREIGN TABLE statement

Define foreign tables.

Organization of this subsection

(1) Specification format

CREATE FOREIGN TABLE-statement ::=
  CREATE FOREIGN TABLE table-name
                       [(column-definition [,column-definition]...)]
                       [partition-key-column-specification]
                       [SERVER foreign-server-name]
                       OPTIONS (foreign-table-option [,foreign-table-option]...)
 
  column-definition ::= column-name data-type
 
  partition-key-column-specification ::= PARTITIONED BY (column-name data-type
                                                       [,column-name data-type]...)
 
  foreign-table-option ::= {foreign-data-format-specification
                           |foreign-data-directory-path-specification
                           |foreign-data-format-option-specification
                           |optional-partitioning-key-column-specification
                           |Iceberg-option-specification}
 
    foreign-data-format-specification ::= FORMAT {CSV|PARQUET|JSON|ICEBERG}
 
    foreign-data-directory-path-specification ::=
                                     LOCATION foreign-data-directory-path-string
      foreign-data-directory-path-string ::= character-string-literal
 
    foreign-data-format-option-specification ::= format-option
                                               [,format-option]...
      format-option ::= {delimiter-character-specification
                        |enclosing-character-specification
                        |header-specification}...
        delimiter-character-specification ::= DELIMITER_CHAR delimiting-character
        enclosing-character-specification ::= ENCLOSING_CHAR {enclosing-character
                                                             |NONE}
        header-specification ::= HEADER_SPEC {YES|NO}
           delimiting-character ::= character-string-literal
           enclosing-character ::= character-string-literal
 
    optional-partitioning-key-column-specification ::= 
                        partition-key-column-option [,partition-key-column-option]...
      partition-key-column-option ::= {property|template-directory-path}
        property ::= {column-name.TYPE {ENUM|INTEGER|DATE|TIME|TIMESTAMP}
                     |column-name.VALUE enumeration-string
                     |column-name.RANGE_MIN {minimum-integer|minimum-value-string} 
                     |column-name.RANGE_MAX {maximum-integer|maximum-value-string}
                     |column-name.FORMAT datetime-format-string
                     |column-name.INTERVAL interval-value
                     |column-name.DIGITS digit-value
                     |column-name.INTERVAL UNIT time-unit}
          enumeration-string ::= character-string-literal
          minimum-integer ::= integer-literal
          minimum-value-string ::= character-string-literal
          maximum-integer ::= integer-literal
          maximum-value-string ::= character-string-literal
          datetime-format-string ::= character-string-literal
          interval-value ::= unsigned-integer-literal
          digit-value ::= unsigned-integer-literal
          time-unit ::= {YEAR|MONTH|WEEK|DAY|HOUR|MINUTE|SECOND}
       template-directory-path ::=
                              LOCATION.TEMPLATE template-directory-path-string
          template-directory-path-string ::= character-string-literal
 
    Iceberg-option-specification ::= Iceberg-option [,Iceberg-option]...
      Iceberg-option ::= {catalog-type-specification
                         |catalog-namespace-specification
                         |catalog-URI-specification
                         |catalog-region-specification
                         |catalog-ID-specification
                         |warehouse-name-specification
                         |token-URI-specification
                         |client-ID-specification
                         |client-secret-specification
                         |allowed-scope-specification}
        catalog-type-specification ::= CATALOG_TYPE {REST|GLUE}
        catalog-namespace-specification ::= CATALOG_NAMESPACE catalog-namespace
            catalog-namespace ::= character-string-literal
        catalog-URI-specification ::= CATALOG_URI catalog-endpoint-string
            catalog-endpoint-string ::= character-string-literal
        catalog-region-specification ::= CATALOG_REGION catalog-region-name
            catalog-region-name ::= character-string-literal
        catalog-ID-specification ::= CATALOG_ID catalog-ID
            catalog-ID ::= character-string-literal
        warehouse-name-specification ::= WAREHOUSE_NAME warehouse-name
            warehouse-name ::= character-string-literal
        token-URI-specification ::= AUTH_TOKEN_URI token-URI
            token-URI ::= character-string-literal
        client-ID-specification ::= AUTH_CLIENT_ID client-ID
            client-ID ::= character-string-literal
        client-secret-specification ::= AUTH_CLIENT_SECRET client-secret
            client-secret ::= character-string-literal
        allowed-scope-specification ::= AUTH_ALLOWED_SCOPES allowed-scope-string
            allowed-scope-string ::= character-string-literal

To Page Top

(2) Explanation of specification format

(a) table-name

Specifies the name of the foreign table to be defined. You cannot specify the table name of a table that has already been defined. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

(b) column-definition

column-definition ::=column-name data-type

Specifies the definition of columns in the foreign table.

Important

  • If ICEBERG is specified in the foreign data format specification, omit the column definitions (column definitions cannot be specified).

  • If PARQUET is specified in the foreign data format specification, column definitions can be omitted.

For the rules when the column definition specification is omitted, see (5) Rules for omitting the column definition specification.

The following specification rules apply:

  • Specify one or more column definitions.

  • It is not necessary to specify all columns of the foreign data to be searched in the column definition.#

  • The order in which column definitions are specified need not be the same as the order of columns in the foreign data to be searched.#

#

If the foreign data is in CSV format without header rows, specify all columns of the foreign data in the column definitions in the correct order.

● column-name

Specifies the column name of the foreign table. The following specification rules apply:

  • The specified column name must exactly match the column name of the foreign data column to be searched.

    However, if the foreign data is in CSV format with no header rows, it is not necessary to match the column names exactly.

  • If two or more column definitions are specified, each column name must be unique.

  • Do not specify a character string in the EXPnnnn_NO_NAME format as a column name. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

● data-type

Specifies the data type of the column in the foreign table. The following table lists the data types that can be specified.

For details about each data type, see 6.2 Data types.

Table 3‒4: Data types that can be specified (CREATE FOREIGN TABLE statement)

No.

Data type

Specification format

1

BIGINT#1

BIGINT

2

INTEGER

INT or INTEGER

3

SMALLINT

SMALLINT

4

DECIMAL

DEC[(m[,n])] or DECIMAL[(m[,n])]

m: Precision (total number of digits)

n: Scaling (number of decimal places)

If m is omitted, 38 is assumed, and if n is omitted, 0 is assumed.

5

NUMERIC*2

NUMERIC[(m[,n])]

m: Precision (total number of digits)

n: Scaling (number of decimal places)

If m is omitted, 38 is assumed, and if n is omitted, 0 is assumed.

6

DOUBLE PRECISION

DOUBLE or DOUBLE PRECISION

7

FLOAT#3

FLOAT

8

REAL

REAL

9

CHARACTER

CHAR(n) or CHARACTER(n)

n: Length of character string (in bytes)

If CHAR or CHARACTER is specified without a length, the length of the character string is assumed to be 1.

10

VARCHAR#4

VARCHAR(n)

n: Maximum length of character string (in bytes)

11

STRING

STRING

12

DATE

DATE

13

TIME

TIME(p) or TIME

p: Fractional seconds precision (number of digits to the right of the decimal point)

You can specify a value of 0, 3, 6, 9, or 12 for p. If TIME is specified, p is assumed to be 0.

14

TIMESTAMP

  • For TIMESTAMP WITHOUT TIME ZONE (time stamp data without time zone)

    Specify in either of the following formats:

    TIMESTAMP(p)

    TIMESTAMP

  • TIMESTAMP WITH TIME ZONE (time stamp data with time zone)

    Specify in either of the following formats:

    TIMESTAMP(p) WITH TIME ZONE

    TIMESTAMP WITH TIME ZONE

p: Fractional seconds precision (number of digits to the right of the decimal point)

You can specify a value of 0, 3, 6, 9, or 12 for p. If (p) is omitted, p is assumed to be 0.

15

BINARY

BINARY(n)

n: Length of the binary data (number of bytes)

If BINARY is specified without a length, the length of the binary data is assumed to be 1.

16

VARBINARY

VARBINARY(n)

n: Maximum length of the binary data (number of bytes)

17

BOOLEAN

BOOLEAN

18

UUID

UUID

19

STRUCT

STRUCT {field-name field-data-type[,field-name field-data-type]...}

field-name:

Specifies the name of a field in the structure data.

field-data-type:

Specifies the data type of the field in the structure data. Specify numeric data, character string data, datetime data, binary data, array data, or structure data for the field data type. The specification format of field data types follows the rules for the description format of each data type.

<Specification example>

STRUCT {"C1" INTEGER,

"C2" CHAR(5),

"C3" STRUCT {"C3_A" INTEGER,

"C3_B" CHAR(5),

"C3_C" DATE}}

20

ARRAY#5

element-data-type ARRAY[maximum-number-of-elements]

element-data-type:

Specifies the array element data type. Specify numeric data, character string data, datetime data, binary data, or structure data for the element data type. The specification format of element data types follows the rules for the description format of each data type.

maximum-number-of-elements:

Specifies the maximum number of array elements. Specify an unsigned integer literal in the range from 2 to 30,000 as the maximum number of elements.

Example:

  • If the element data type is CHAR(5) and the maximum number of elements is 20

    CHAR(5) ARRAY[20]

  • If the element data type is INTEGER and the maximum number of elements is 5

    INTEGER ARRAY[5]
#1

If the integer data type format is a legacy format, when BIGINT type is specified, HADB assumes that the INTEGER type is specified.

#2

If type NUMERIC is specified, HADB assumes that type DECIMAL is specified for the data type.

#3

If a FLOAT type is specified, HADB assumes that a DOUBLE PRECISION type is specified.

#4

A VARCHAR type column whose defined length exceeds 32,000 bytes cannot be specified.

#5

Data type of the array type. For considerations when defining array-type columns, see Defining an array-type column [column store table] in the HADB Setup and Operation Guide.

Important

  • If PARQUET is specified in the foreign data format specification, the following data types cannot be specified for the columns of the foreign table:

    • TIME(0), TIME(12), TIMESTAMP(0) WITHOUT TIME ZONE, TIMESTAMP(12) WITHOUT TIME ZONE, TIMESTAMP(0) WITH TIME ZONE, and TIMESTAMP(12) WITH TIME ZONE

    • A STRUCT type that specifies one of the above data types for the field data type, or an ARRAY type that specifies one of the above data types for the element data type.

  • If CSV is specified in the foreign data format specification, the following data types cannot be specified for the columns of the foreign table:

    • TIME(12), TIMESTAMP(12) WITHOUT TIME ZONE, TIMESTAMP(12) WITH TIME ZONE, UUID type, STRUCT type, and ARRAY type

  • If JSON is specified in the foreign data format specification, the following data types cannot be specified for the columns of the foreign table:

    • TIME(12) , TIMESTAMP(12) WITHOUT TIME ZONE, TIMESTAMP(12) WITH TIME ZONE, and UUID type

    • A STRUCT type that specifies any of the above data types for the field data type, or an ARRAY type that specifies any of the above data types for the element data type.

(c) partition-key-column-specification

partition-key-column-specification ::= PARTITIONED BY (column-name data-type [,column-name data-type]…)

Defines the partition key columns. Defining a partition key column allows you to narrow down the foreign data files to be searched.

Important

If ICEBERG is specified in the foreign data format specification, partition key column specification cannot be specified. However, if partitions are defined in the referenced Iceberg table, filtering using those partitions may be enabled during the search of the foreign table.

Note

  • The specification of "column-name data-type" in a partition key column specification is called a partition key column definition. Up to 16 partition key column definitions can be specified.

  • A file that contains foreign data is called a foreign data file.

● column-name

Specifies the column name of a partition key column. Specify an arbitrary name.

The following specification rules apply:

  • You cannot specify the same name as the column name in the foreign table.

  • When specifying two or more partition key column definitions, the same name cannot be specified.

● data-type

Specifies the data type of the partition key column. Specify one of the following data types:

  • BIGINT#1

  • INTEGER#2

  • SMALLINT

  • CHAR

  • VARCHAR

  • STRING

#1

If the integer data type format is a legacy format, when BIGINT type is specified, HADB assumes that the INTEGER type is specified.

#2

If the integer data type format is a legacy format, when INTEGER type is specified as the data type, HADB assumes that the SMALLINT type is specified.

For details about data types, see 6.2 Data types.

How to use partition key columns

  • When defining a foreign table

    Partition key columns are defined by the CREATE FOREIGN TABLE statement that defines the foreign table, and the values that the partition key columns can take are defined by optional partitioning key column specification in the foreign table options. Based on the value of the partition key column, the path of the directory containing the foreign data file to be searched (target directory for foreign data search) is determined. This allows you to narrow down the foreign data files to be searched.

    Example:
    CREATE FOREIGN TABLE "FT1"
       :
    PARTITIONED BY ("YEAR" INTEGER)          ...1
       :
    ,LOCATION 's3://data/'                   ...2
       :
    ,"YEAR".TYPE ENUM                        ...3
    ,"YEAR".VALUE '2024,2025'                ...3
    ,LOCATION.TEMPLATE 's3://data/${YEAR}/'  ...3

    Explanation:

    1. The partition key column YEAR (underlined) is defined.

    2. Specification of the target directory for foreign data search. If no partition key column is defined, the search targets foreign data in foreign data files under the s3://data/ directory (including subdirectories).

    3. Optional partitioning key column specification of the foreign table option. This specification determines the target directory for foreign data search. In the case of the above specification, the following directories are the target directory for foreign data search, and the foreign data in the foreign data files under these directories (including subdirectories) will be searched.

      • s3://data/2024/

      • s3://data/2025/

    For details about the optional partitioning key column specification, see (e) foreign-table-option. See (7) Examples for an example of specifying a CREATE FOREIGN TABLE statement with a partition key column.

  • When searching a foreign table (Part 1)

    When searching a foreign table, you can further narrow down the foreign data files to be searched by specifying a narrowing condition for the partition key column as a predicate in the WHERE clause.

    Example:
    SELECT "C1","C2" FROM "FT1"
    WHERE "YEAR" = '2025'

    The search condition in the WHERE clause specifies a narrowing condition for the partition key column YEAR (underlined part). When executing the above SELECT statement, the following directory becomes the target directory for foreign data search, and the foreign data in the foreign data files under this directory (including subdirectories) become the search target.

    • s3://data/2025/

    By specifying the predicate in the WHERE clause in the following format, you can narrow down the foreign data files to be searched.

    • Comparison predicate

      You can specify the partition key column only for comparison predicates in the following format.

      partition-key-column comparison predicate {integer-literal|character-string-literal}

      Example:

      WHERE "PARTITION_KEY" = 1

      PARTITION_KEY is the partition key column.

    • IN predicate

      You can specify the partition key column only for IN predicates in the following format.

      partition-key-column [NOT] IN ({integer-literal|character-string-literal}[,{integer-literal|character-string-literal}...])

      Example:

      WHERE "PARTITION_KEY" IN (1,2,3)

    • BETWEEN predicate

      You can specify the partition key column only for BETWEEN predicates in the following format.
      partition-key-column [NOT] BETWEEN {integer-literal|character-string-literal} AND {integer-literal|character-string-literal}
      Example:
      WHERE "PARTITION_KEY" BETWEEN 1 AND 10
    Note

    See (7) Examples for an example of specifying a SELECT statement with a partition key column.

  • When searching a foreign table (Part 2)

    You can specify the partition key column when searching a foreign table. For example, you can do the following:

    • Specify the partition key column in the selection expression to reference the values of the partition key column

    • Specify the partition key column in the GROUP BY clause to group data by their values

    • Specify the partition key column in the ORDER BY clause to sort the result rows by their values

(d) foreign-server-name

SERVER foreign-server-name

Specifies the name of the foreign server to be used by the foreign table you are defining. Specifies the name of the foreign server required to access the foreign data to be loaded into the foreign table.

Specify the foreign server name defined in the CREATE SERVER statement. You cannot specify a foreign server name that does not exist.

The specification rules and notes are as follows:

  • If foreign data is stored in object storage (when the directory path string of the foreign data is specified in S3 URI format), be sure to specify the SERVER option.

  • If foreign data is stored in file storage (when the directory path string of the foreign data is specified in FILE URI format), omit the SERVER option.

  • If ICEBERG is specified in the foreign data format specification, the foreign server specified here is used when accessing the data files and metadata of the foreign data.

(e) foreign-table-option

OPTIONS (foreign-table-option [,foreign-table-option]...)
 
  foreign-table-option ::= {foreign-data-format-specification
                           |foreign-data-directory-path-specification
                           |foreign-data-format-option-specification
                           |optional-partitioning-key-column-specification
                           |Iceberg-option-specification}

The foreign table option specifies the data format of the foreign data, the path of the target directory for foreign data search, and information about the Iceberg table.

Important

  • The foreign data format specification in the foreign table options must be specified.

  • Whether other foreign table options are required depends on the value specified in the foreign data format specification. The following table shows the requirements for specification.

Table 3‒5: Requirement for specifying foreign table options

Foreign table option

Specified value in the foreign data format specification

CSV

PARQUET

JSON

ICEBERG

foreign-data-directory-path-specification

R

R

R

N

foreign-data-format-option-specification

O

N

N

N

optional-partitioning-key-column-specification

O

O

O

N

Iceberg-option-specification

N

N

N

R
Legend:

R: Required

O: Optional

N: Cannot be specified

● foreign-data-format-specification
foreign-data-format-specification ::= FORMAT {CSV|PARQUET|JSON|ICEBERG}

Specifies the data format of the foreign data. Select one of the following:

  • CSV: Specify if the foreign data is in CSV format.

  • PARQUET: Specify if the foreign data is in PARQUET format.

  • JSON: Specify if the foreign data is in JSON format.

  • ICEBERG: Specify if accessing foreign data using an Iceberg table.

All foreign data to be searched will be treated as the data format specified here.

Note

The foreign data format specification is required. However, it cannot be specified more than once.

● foreign-data-directory-path-specification
foreign-data-directory-path-specification ::= LOCATION foreign-data-directory-path-string

Specifies the path to the directory containing the foreign data files to be searched when retrieving foreign tables (target directory for foreign data search) in the form of a character string literal.

Important

  • If ICEBERG is specified in the foreign data format specification, the directory path specification for foreign data cannot be specified.

  • If a data format other than ICEBERG is specified in the foreign data format specification, the directory path specification for foreign data is mandatory. However, the directory path specification for foreign data cannot be specified multiple times.

The format for specifying the directory path string of foreign data differs depending on whether the foreign data is stored in object storage or file storage. The following shows the examples of specifications.

  • When foreign data is stored in object storage:

    LOCATION 's3://data-bucket/ftbl/T1/'

    Specify the directory path string for foreign data in S3 URI format. In this case, foreign data in the foreign data files located under the directory s3://data-bucket/ftbl/T1/ (including subdirectories) will be searched when searching the foreign table.

  • When foreign data is stored in file storage:

    LOCATION 'file:///data/ftbl/T1/'

    Specify the directory path string for foreign data in FILE URI format. In this case, foreign data in the foreign data files located under the directory /data/ftbl/T1/ (including subdirectories) will be searched when searching the foreign table.

    Note that the directory specified here (in the above example, /data/ftbl/T1/) must have read and execution permissions granted to the HADB administrator. In addition, all files within the specified directory must have read permissions granted to the HADB administrator.

The following specification rules apply to the foreign data directory path specification:

  • Specify the foreign data directory path string to 1,024 bytes or less.#

  • Specify s3:// at the beginning and / at the end of the foreign data directory path string when the foreign data is stored in object storage.#

  • Specify file:// at the beginning and / at the end of the foreign data directory path string when the foreign data is stored in file storage.#

#

If there are spaces before or after the foreign data directory path string, those spaces are removed. This rule is applied to foreign data directory path strings from which spaces have been removed.

Note the following points:

  • When searching foreign tables, foreign data in all foreign data files under the directory specified here will be searched (foreign data under subdirectories will also be searched). Therefore, do not store data that you do not want the HADB server to load under this directory.

  • Foreign data in the same format should be stored under the directory specified here (including subdirectories). Mixture of foreign data in different data formats or with different column configurations may result in errors when searching foreign tables.

Note

The foreign data directory path specification is required. However, it cannot be specified more than once.

● foreign-data-format-option-specification
foreign-data-format-option-specification ::= format-option
                                           [,format-option]...
 
 format-option ::= {delimiter-character-specification|enclosing-character-specification|header-specification}
   delimiter-character-specification ::= DELIMITER_CHAR delimiting-character
   enclosing-character-specification ::= ENCLOSING_CHAR {enclosing-character|NONE}
   header-specification ::= HEADER_SPEC {YES|NO}

Specify the foreign data format option specification only when the foreign data is in CSV format.

The foreign data format option specification allows you to specify delimiting characters, enclosing characters, etc., for each data in CSV format.

delimiter-character-specification
delimiter-character-specification ::= DELIMITER_CHAR delimiting-character

Specifies the delimiting character in the form of a character string literal that delimits each piece of foreign data in CSV format.

The following specification rules apply:

  • Specify a 1-byte character string as the delimiting character.

  • If the delimiter character specification is omitted, the comma (,) is assumed as the delimiting character.

  • To define a single quotation mark (') as a delimiting character, specify two single quotation marks.

  • The delimiter character specification cannot be specified more than once.

enclosing-character-specification
enclosing-character-specification ::= ENCLOSING_CHAR {enclosing-character|NONE}

Specifies the enclosing characters surrounding each piece of foreign data in CSV format in the form of character string literals.

The following specification rules apply:

  • If there are no enclosing characters in the foreign data, specify NONE.

  • Specify a 1-byte character string for the enclosing characters.

  • If the enclosing character specification is omitted, the double quotation mark ( " ) is assumed as the enclosing character.

  • To define a single quotation mark ( ' ) as an enclosing character, specify two single quotation marks.

  • The enclosing character specification cannot be specified more than once.

header-specification
header-specification ::= HEADER_SPEC {YES|NO}

Specifies whether the first line of foreign data in CSV format has a header line.

YES

Specify YES if there is a header line on the first line. In this case, the first line is treated as a header line and the second and subsequent lines as data lines.

If the header specification is omitted, YES is assumed.

NO

Specify NO if there is no header line on the first line. In this case, the first and subsequent lines are treated as data lines.

Note that the header specification cannot be specified more than once.

● optional-partitioning-key-column-specification
optional-partitioning-key-column-specification ::= partition-key-column-option[,partition-key-column-option]...
  partition-key-column-option::={property|template-directory-path}

For optional partitioning key column specification, specify properties and the template directory path. The property defines the values that the partition key column can take. The template directory path is a character string with the partition key sequence as a variable. The target directory for foreign data search is determined based on the specification of the property and the template directory path (the foreign data files to be searched are determined).

The following is an example of an optional partitioning key column specification.

,"YEAR".TYPE ENUM                         ...1
,"YEAR".VALUE '2024,2025'                 ...1
,LOCATION.TEMPLATE 's3://data/${YEAR}/'   ...2

Explanation:

  1. Specifies the property.

    The underlined part specifies the partition key column defined in the partition key column specification.

    The partition key column YEAR is defined to have the values 2024 and 2025.

  2. Specifies the template directory path.

    The underlined part specifies the character string that makes the partition key sequence a variable. This part is the variable value. In this case, when searching a foreign table, the foreign data in the foreign data files under the following directories (including subdirectories) will be the target of the search (the following directories are the target directories of the foreign data search).

    • s3://data/2024/

    • s3://data/2025/

As shown in the example above, properties can be specified multiple times, but the template directory path can be specified only once.

Important

When specifying the optional partitioning key column specification, specify the partition key column specification. If you omit the optional partitioning key column specification, omit the partition key column specification as well.

property
property ::= {column-name.TYPE {ENUM|INTEGER|DATE|TIME|TIMESTAMP}
             |column-name.VALUE enumeration-string
             |column-name.RANGE_MIN {minimum-integer|minimum-value-string}
             |column-name.RANGE_MAX {maximum-integer|maximum-value-string}
             |column-name.FORMAT datetime-format-string
             |column-name.INTERVAL interval-value
             |column-name.DIGITS digit-value
             |column-name.INTERVAL. UNIT time-unit}

The property defines the values that the partition key column can take.

Specify the partition key column for the column name. The same type of property cannot be specified for the same column name.

Important

The column-name.TYPE specification is required. The properties that can be specified differ depending on the value specified for the column-name.TYPE.

  • When ENUM is specified for column-name.TYPE

    Properties that can be specified

    Description and specification example

    Whether specification is required

    column-name.TYPE ENUM

    Specify if you want to specify the possible values of the partition key column as a character string.

    Specification example

    "MONTH".TYPE ENUM

    For the underlined part, specify the partition key column.

    Required

    column-name.VALUE enumeration-string

    • Specify the enumeration string in the form of a character string literal. When you specify multiple character strings, use a comma (,) to separate the character strings.

    • Spaces in a string are considered part of the string.

    • A maximum of 100 character strings can be specified.

    • The maximum length of each character string specified in an enumeration string is 100 bytes.

    • The maximum length of an enumeration string is 10,100 bytes.

    Specification example

    "MONTH".VALUE 'January,February,March'

    The underlined parts indicate each string to be specified in the enumeration string. In this case, the values of the partition key column MONTH are January, February, and March.

    Required
    Note

    • If the data type of the partition key column is BIGINT, INTEGER, or SMALLINT

      If each string specified in the enumeration string cannot be converted to an integer value of the data type of the partition key column, the CREATE FOREIGN TABLE statement will result in an error. The conversion rules follow the rules for converting character string data to numeric data in the scalar function CONVERT.

    • If the data type of the partition key column is CHAR, VARCHAR, or STRING

      If the data length of each string specified in the enumeration string exceeds the data length of the data type of the partition key column, the CREATE FOREIGN TABLE statement will result in an error.

  • When INTEGER is specified for column-name.TYPE

    Properties that can be specified

    Description and specification example

    Whether specification is required

    column-name.TYPE INTEGER

    Specify if you want to specify the range of possible values (minimum and maximum values) for the partition key column as integer values.

    Specification example

    "DAY".TYPE INTEGER

    For the underlined part, specify the partition key column.

    Required

    column-name.RANGE_MIN minimum-integer

    The minimum integer specifies the minimum possible value of the partition key column in the form of an integer literal.

    Specification example

    "DAY".RANGE_MIN 1

    In this case, the minimum possible value of the partition key column DAY is 1.

    Required

    column-name.RANGE_MAX maximum-integer

    • The maximum integer specifies the maximum possible value of the partition key column in the form of an integer literal.

    • Specify a value for the maximum integer that is equal to or greater than the minimum integer.

    Specification example

    "DAY".RANGE_MAX 5

    In this case, the maximum possible value of the partition key column DAY is 5.

    Required

    column-name.INTERVAL interval-value

    • The interval value specifies the interval of possible values for the partition key column.

    • Specify an unsigned integer literal in the range from 1 to 1,024 for the interval value.

    • If you omit the specification, the interval value is assumed to be 1.

    Specification example

    "DAY".INTERVAL 2

    "DAY".RANGE_MIN 1

    "DAY".RANGE_MAX 5

    When specified as above, the values of the partition key column DAY are 1, 3, and 5.

    Optional

    column-name.DIGITS digit-value

    • The digit value specifies the digit value in the value of the partition key column.

    • Specify an unsigned integer literal in the range from 1 to 20 for the digit value.

    • If the specified digit value is greater than the digit value of integer values that the partition key column can take, the integer values of the partition key column are padded with zeros in the front to make the specified digit value.

    • If the integer values that the partition key column can take are negative, and the number of digits excluding the negative sign (-) is greater than the specified number of digits, the value will be padded with zeros at the front to ensure that the number of digits excluding the negative sign (-) matches the specified number of digits.

    • If the specification is omitted, or if the digit value specified is less than or equal to the digit value of integer values that the partition key column can take, the integer values that the partition key column can take are not padded with zeros from the front.

    Specification example

    "DAY".DIGITS 3

    "DAY".RANGE_MIN 1

    "DAY".RANGE_MAX 10

    When specified as above, the values of the partition key column DAY are 1, 2, ..., 9, 10. The values used as variable values in the template directory path are 001, 002, ..., 009, 10.

    Optional
    Note

    • If the data type of the partition key column is BIGINT, INTEGER, or SMALLINT

      If the integer value specified for the minimum or maximum integer exceeds the range of integer values that can be represented by the data type of the partition key column, the CREATE FOREIGN TABLE statement will result in an error.

    • If the data type of the partition key column is CHAR, VARCHAR, or STRING

      If the integer value specified as the minimum integer or maximum integer is converted to a character string and the converted data length exceeds the data length of the data type of the partition key column, the CREATE FOREIGN TABLE statement will result in an error. The conversion rules follow the rules for converting numeric data to character string data in the scalar function CONVERT.

  • When DATE, TIME, or TIMESTAMP is specified for column-name.TYPE

    Important

    If the data type of the partition key column is BIGINT, INTEGER, or SMALLINT, DATE, TIME, or TIMESTAMP cannot be specified for column-name.TYPE.

    Properties that can be specified

    Description and specification example

    Whether specification is required

    column-name.TYPE DATE

    • Be sure to specify one of the properties listed on the left.

    • Specify when specifying a range of possible values for a partition key column as a range specification (minimum and maximum values) by datetime values.

    Specification example (for TYPE DATE)

    "DAY".TYPE DATE

    For the underlined part, specify the partition key column.

    Specification example (for TYPE TIME)

    "END_TIME".TYPE TIME

    For the underlined part, specify the partition key column.

    Specification example (for TYPE TIMESTAMP)

    "LOGIN_TSP".TYPE TIMESTAMP

    For the underlined part, specify the partition key column.

    Required

    column-name.TYPE TIME

    column-name.TYPE TIMESTAMP

    column-name.RANGE_MIN minimum-value-string

    • The minimum value string specifies the minimum datetime value that the partition key column can take, in the form of a character string literal.

    • The maximum length of the minimum value string is 100 bytes.

    Specification example (for TYPE DATE)

    "DAY".RANGE_MIN '2020-01-01'

    In this case, the minimum possible value for the partition key column DAY is 2020-01-01.

    Specification example (for TYPE TIME)

    "END_TIME".RANGE_MIN '10:00:00'

    In this case, the minimum possible value of the partition key column END_TIME is 10:00:00.

    Specification example (for TYPE TIMESTAMP)

    "LOGIN_TSP".RANGE_MIN '2020-06-01_00:00:00'

    In this case, the minimum possible value for the partition key column LOGIN_TSP is 2020-06-01_00:00:00.

    Required

    column-name.RANGE_MAX maximum-value-string

    • The maximum value string specifies the maximum datetime value that the partition key column can take, in the form of a character string literal.

    • The maximum length of the maximum value string is 100 bytes.

    • Specify a datetime value for the maximum value string that is equal to or greater than the minimum value string.

    Specification example (for TYPE DATE)

    "DAY".RANGE_MAX '2025-03-31'

    In this case, the maximum possible value of the partition key column DAY is 2025-03-31.

    Specification example (for TYPE TIME)

    "END_TIME".RANGE_MAX '12:00:00'

    In this case, the maximum possible value of the partition key column END_TIME is 12:00:00.

    Specification example (for TYPE TIMESTAMP)

    "LOGIN_TSP".RANGE_MAX '2025-03-31_00:00:00'

    In this case, the maximum possible value of the partition key column LOGIN_TSP is 2025-03-31_00:00:00.

    Required

    column-name.FORMAT datetime-format-string

    • The datetime format string specifies the datetime format of the datetime values specified in the minimum and maximum value strings.

    • The values that can be specified for the datetime format string is the same as the value that can be specified for the datetime format of the scalar function CONVERT. For details about the datetime format of the scalar function CONVERT, see (3) Datetime format elements and rules in 8.13.5 CONVERT.

    • The maximum length of the datetime format string is 64 bytes.

    • The specified datetime format string is converted to a datetime value with the scalar function CONVERT as shown below. If the conversion is not possible, the SQL statement will result in an error.

      CONVERT(minimum-value-string,TYPE-specification-value,datetime-format-string)

      CONVERT(maximum-value-string,TYPE-specification-value,datetime-format-string)

    Specification example (for TYPE DATE)

    "DAY".FORMAT 'YYYY-MM-DD'

    The above defines the datetime values to be specified in the minimum and maximum value strings in the format YYYY-MM-DD.

    Specification example (for TYPE TIME)

    "END_TIME".FORMAT 'HH:MI:SS'

    The above defines the datetime values to be specified in the minimum and maximum value strings in the format HH:MI:SS.

    Specification example (for TYPE TIMESTAMP)

    "LOGIN_TSP".FORMAT 'YYYY-MM-DD"_"HH:MI:SS'

    The above defines the datetime values to be specified in the minimum and maximum value strings in the format YYYY-MM-DD_HH:MI:SS.

    Required

    column-name.INTERVAL interval-value

    • The interval value specifies the interval of possible values for the partition key column.

    • Specify an unsigned integer literal in the range from 1 to 1,024 for the interval value.

    • If you omit the specification, the interval value is assumed to be 1.

    Specification example (for TYPE DATE)

    "DAY".INTERVAL 7

    "DAY".RANGE_MIN '2025-01-01'

    "DAY".RANGE_MAX '2025-01-20'

    "DAY".INTERVAL.UNIT DAY

    When specified as above, the values of the partition key column DAY are 2025-01-01, 2025-01-08, and 2025-01-15.

    Specification example (for TYPE TIME)

    "END_TIME".INTERVAL 30

    "END_TIME".RANGE_MIN '10:00:00'

    "END_TIME".RANGE_MAX '11:00:00'

    "END_TIME".INTERVAL.UNIT MINUTE

    When specified as above, the values of the partition key column END_TIME are 10:00:00, 10:30:00, and 11:00:00.

    Specification example (for TYPE TIMESTAMP)

    "LOGIN_TSP".INTERVAL 4

    "LOGIN_TSP".RANGE_MIN '2016-01-01_00:00:00'

    "LOGIN_TSP".RANGE_MAX '2025-01-01_00:00:00'

    "LOGIN_TSP".INTERVAL.UNIT YEAR

    When specified as above, the values of the partition key column LOGIN_TSP are 2016-01-01_00:00:00, 2020-01-01_00:00:00, and 2024-01-01_00:00:00.

    Optional

    column-name.INTERVAL.UNIT time-unit

    • Specify the interval value unit for the time unit. Specify YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND.

    • If TYPE DATE is specified, specify YEAR, MONTH, WEEK, or DAY.

    • If TYPE TIME is specified, specify HOUR, MINUTE, or SECOND.

    • If TYPE TIMESTAMP is specified, specify YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND.

    • If you omit the specification, the time unit is assumed to be as follows.

      If TYPE is DATE, DAY is assumed.

      If TYPE is TIME or TIMESTAMP, SECOND is assumed.

    Specification example (for TYPE DATE)

    "DAY".INTERVAL.UNIT DAY

    In this case, the unit of the interval value is days.

    Specification example (for TYPE TIME)

    "END_TIME".INTERVAL.UNIT MINUTE

    In this case, the unit of the interval value is minutes.

    Specification example (for TYPE TIMESTAMP)

    "LOGIN_TSP".INTERVAL.UNIT SECOND

    In this case, the unit of the interval value is seconds.

    Optional
    Note

    If the datetime values converted from the minimum value string or maximum value string cannot be converted to the data type of the partition key column using the datetime format string, the CREATE FOREIGN TABLE statement will result in an error. The conversion rules follow the rules specified for the datetime format when using the scalar function CONVERT.

Examples of specifying properties are shown below.

Example 1:
,"DAY".TYPE DATE
,"DAY".RANGE_MIN '2024-01-01'
,"DAY".RANGE_MAX '2024-05-31'
,"DAY".FORMAT 'YYYY-MM-DD'
,"DAY".INTERVAL 2
,"DAY".INTERVAL.UNIT MONTH

In this case, the values of the partition key column DAY are 2024-01-01, 2024-03-01, and 2024-05-01.

Example 2:
,"END_TIME".TYPE TIME
,"END_TIME".RANGE_MIN '10:00:00'
,"END_TIME".RANGE_MAX '12:00:00'
,"END_TIME".FORMAT 'HH:MI:SS'
,"END_TIME".INTERVAL 30
,"END_TIME".INTERVAL.UNIT MINUTE

In this case, the values of the partition key column END_TIME are 10:00:00, 10:30:00, 11:00:00, 11:30:00, and 12:00:00.

Example 3:
,"LOGIN_TSP".TYPE TIMESTAMP
,"LOGIN_TSP".RANGE_MIN '2024-06-01_00:00:00'
,"LOGIN_TSP".RANGE_MAX '2024-06-03_00:00:00'
,"LOGIN_TSP".FORMAT 'YYYY-MM-DD"_"HH:MI:SS'
,"LOGIN_TSP".INTERVAL 12
,"LOGIN_TSP".INTERVAL.UNIT HOUR

In this case, the values of the partition key column LOGIN_TSP are 2024-06-01_00:00:00, 2024-06-01_12:00:00, 2024-06-02_00:00:00, 2024-06-02_12:00:00, and 2024-06-03_00:00:00.

template-directory-path
template-directory-path ::= LOCATION.TEMPLATE template-directory-path-string

The target directory for foreign data search is determined based on the specification of the template directory path string. The value of the partition key string defined in the property can be specified as a variable in the template directory path string. An example of specifying a template directory path string is shown below.

LOCATION.TEMPLATE 's3://data/${YEAR}/${DAY}/'

The underlined parts are variables. If the value of the partition key column YEAR defined in the property is 2024 and 2025, and the value of the partition key column DAY is 01, 02, and 03, the following paths are the paths of the target directory for foreign data search.

  • 's3://data/2024/01/'

  • 's3://data/2024/02/'

  • 's3://data/2024/03/'

  • 's3://data/2025/01/'

  • 's3://data/2025/02/'

  • 's3://data/2025/03/'

When searching foreign tables, foreign data in foreign data files stored under the above directories (including subdirectories) are the target of the search.

Specification rules

  • Specify the template directory path string in the form of a character string literal.

  • In the template directory path string, the value of the partition key column defined in the property can be specified as a variable in the following format.

    ${partition-key-column-name}

    The underlined parts of the example below are the relevant locations.

    LOCATION.TEMPLATE 's3://data/${YEAR}/${DAY}/'

    Note that the column names in the partition key columns are case-sensitive.

  • Specify the path name specified in the foreign data directory path string (LOCATION option) as the first part of the path name specified in the template directory path string (LOCATION.TEMPLATE option).

    Example:

    LOCATION 's3://data/'
LOCATION.TEMPLATE 's3://data/${YEAR}/${DAY}/'

    The underlined pathname specifications must match.

  • Specify / at the end of the template directory path string.

  • The maximum length of the template directory path string is 1,024 bytes.

  • Specify variables corresponding to all partition key columns in the template directory path string.

  • You can specify multiple variables corresponding to the same partition key column in the template directory path string.

    Example:

    LOCATION.TEMPLATE 's3://data/${YEAR}/${YEAR}/'

    Two variables corresponding to the partition key column YEAR are specified in the underlined portion.

    If the partition key column YEAR can take values 2023, 2024, and 2025, the path for the target directory for foreign data search will be as follows:

    • 's3://data/2023/2023/'

    • 's3://data/2024/2024/'

    • 's3://data/2025/2025/'

If the template directory path specification is omitted

If the template directory path specification is omitted, the template directory path string is assumed to be a concatenated character string of the foreign data directory path string and the column name of the partition key column.

Example:

  • Foreign data directory path string: 's3://data/'

  • Values for partition key column YEAR: 2024 and 2025

  • Values for partition key column MONTH: 01, 02, and 03

If the template directory path is omitted in the above condition, the following character string is assumed for the template directory path string.

's3://data/YEAR=${YEAR}/MONTH=${MONTH}/'

In this case, the path of the target directory for foreign data search is as follows. When searching foreign tables, foreign data in files under these directories will be searched.

  • 's3://data/YEAR=2024/MONTH=01/'

  • 's3://data/YEAR=2024/MONTH=02/'

  • 's3://data/YEAR=2024/MONTH=03/'

  • 's3://data/YEAR=2025/MONTH=01/'

  • 's3://data/YEAR=2025/MONTH=02/'

  • 's3://data/YEAR=2025/MONTH=03/'

● Iceberg-option-specification
Iceberg-option-specification ::= Iceberg-option[,Iceberg-option]...
 
    Iceberg-option ::= {catalog-type-specification#1
                       |catalog-namespace-specification#1
                       |catalog-URI-specification
                       |catalog-region-specification
                       |catalog-ID-specification
                       |warehouse-name-specification
                       |token-URI-specification#2
                       |client-ID-specification#2
                       |client-secret-specification#2
                       |allowed-scope-specification#2}#3
#1

This option must be specified.

#2

Specify either all of these options or omit them all.

#3

Each Iceberg option cannot be specified multiple times. For example, specifying the catalog type twice is not allowed.

When defining a foreign table that references an Iceberg table, specify information related to the Iceberg table. If ICEBERG is specified in the foreign data format specification, the Iceberg option specification must be specified.

catalog-type-specification
catalog-type-specification ::= CATALOG_TYPE {REST|GLUE}

Specifies the type of catalog used by Iceberg.

  • REST

    Specify when using the REST Catalog. When REST is specified, the only supported authentication method is OAuth 2.0 (Client Credentials Grant). Other authentication methods cannot be used.

  • GLUE

    Specify when using the Glue Catalog. When GLUE is specified, the authentication information specified in the CREATE SERVER statement is used when accessing the Glue Catalog or Amazon S3. Therefore, the Glue Catalog and the S3 bucket must exist within the same AWS account.

catalog-namespace-specification
catalog-namespace-specification ::= CATALOG_NAMESPACE catalog-namespace
    catalog-namespace ::= character-string-literal

Specifies the catalog namespace used by Iceberg. The catalog namespace can be a string from 1 to 100 bytes in length.

catalog-URI-specification
catalog-URI-specification ::= CATALOG_URI catalog-endpoint-string
    catalog-endpoint-string ::= character-string-literal

Specify this option when accessing the catalog used by Iceberg via an endpoint.

The following specification rules apply:

  • Specify a string starting with https:// or http:// for the catalog endpoint string.

  • The catalog endpoint string should specify the endpoint in URI format. For example, if the endpoint is https://XXXX:1234, specify 'https://XXXX:1234' as the catalog endpoint string.

  • The catalog endpoint string can be a string from 1 to 100 bytes in length.

  • If REST is specified as the catalog type, the catalog URI must be specified. If GLUE is specified as the catalog type, the catalog URI cannot be specified.

catalog-region-specification
catalog-region-specification ::= CATALOG_REGION catalog-region-name
    catalog-region-name ::= character-string-literal

Specifies the region of the catalog used by Iceberg.

The following specification rules apply:

  • The catalog region name can be a string from 1 to 100 bytes in length.

  • If GLUE is specified as the catalog type, the catalog region must be specified. If REST is specified as the catalog type, the catalog region cannot be specified.

catalog-ID-specification
catalog-ID-specification ::= CATALOG_ID catalog-ID
    catalog-ID ::= character-string-literal

Specifies the catalog ID of the catalog used by Iceberg.

The following specification rules apply:

  • The catalog ID can be a string from 1 to 100 bytes in length.

  • If GLUE is specified as the catalog type, the catalog ID must be specified. If REST is specified as the catalog type, the catalog ID cannot be specified.

warehouse-name-specification
warehouse-name-specification ::= WAREHOUSE_NAME warehouse-name
    warehouse-name ::= character-string-literal

Specifies the name of the warehouse used by Iceberg.

The following specification rules apply:

  • The warehouse name can be a string from 1 to 100 bytes in length.

  • If REST is specified as the catalog type, specifying the warehouse name is optional. For example, when using Lakekeeper as the REST Catalog, specifying the warehouse name is necessary to explicitly indicate the data storage location.

  • If GLUE is specified as the catalog type, specifying the warehouse name is not allowed.

token-URI-specification
token-URI-specification ::= AUTH_TOKEN_URI token-URI
    token-URI ::= character-string-literal

Specifies the URL for obtaining the token from the ID provider used when connecting to the Rest Catalog.

The following specification rules apply:

  • The token URI can be a string from 1 to 400 bytes in length.

  • Specify a string starting with http:// or https:// for the token URI.

  • If GLUE is specified as the catalog type, specifying the token URI is not allowed.

client-ID-specification
client-ID-specification ::= AUTH_CLIENT_ID client-ID
    client-ID ::= character-string-literal

Specifies the client ID of the ID provider used when connecting to the Rest Catalog.

The following specification rules apply:

  • The client ID can be a string from 1 to 100 bytes in length.

  • If GLUE is specified as the catalog type, specifying the client ID is not allowed.

client-secret-specification
client-secret-specification ::= AUTH_CLIENT_SECRET client-secret
    client-secret ::= character-string-literal

Specifies the client secret of the ID provider used when connecting to the Rest Catalog.

The following specification rules apply:

  • The client secret can be a string from 1 to 100 bytes in length.

  • If GLUE is specified as the catalog type, specifying the client secret is not allowed.

allowed-scope-specification
allowed-scope-specification ::= AUTH_ALLOWED_SCOPES allowed-scope-string
    allowed-scope-string ::= character-string-literal

Specifies the client scopes allowed by the ID provider used when connecting to the Rest Catalog.

The following specification rules apply:

  • The allowed scope string can be a string from 1 to 255 bytes in length.

  • If GLUE is specified as the catalog type, specifying the allowed scope is not permitted.

  • When specifying multiple allowed client scopes, separate them with half-width spaces. The following is an example of specifying the allowed client scopes email, profile, and role_list:

    AUTH_ALLOWED_SCOPES 'email profile role_list'

To Page Top

(3) Privileges required at execution

To execute the CREATE FOREIGN TABLE statement, the CONNECT privilege and the schema definition privilege are required.

To Page Top

(4) Rules

  1. Define a schema before defining a foreign table.

  2. A foreign table can only be defined in the schema owned by the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot define a foreign table in a schema owned by another HADB user.

  3. A maximum of 30,000 base tables and foreign tables can be defined (excluding the base tables of dictionary tables and system tables).

  4. A maximum of 4,000 columns can be defined in one foreign table. When defining a partition key column, count one partition key column as one column.

  5. When searching a foreign table, if there is no foreign data to be searched, the number of rows in the search result of the foreign table is zero.

  6. Access to foreign data is not performed when defining foreign tables. Access to foreign data is performed when retrieving foreign tables. Therefore, if you change the foreign data after defining a foreign table, the retrieval results of the foreign table may change.

  7. When PARQUET or ICEBERG is specified in the foreign data format specification, access is made to foreign data in PARQUET format. The following table shows the correspondence between the data type of each column of foreign data in PARQUET format and the HADB data type.

    Table 3‒6: Correspondence between the data type of each column of foreign data in PARQUET format and the HADB data type (part 1)

    Data types of foreign data columns in PARQUET format

    Corresponding HADB data types

    SMALLINT#1

    INTEGER#2

    BIGINT

    DECIMAL, NUMERIC

    REAL

    DOUBLE PRECISION, FLOAT

    NULL

    Y

    Y

    Y

    Y

    Y

    Y

    INT(bitWidth=8, isSigned=false)

    Y

    Y

    Y

    N

    N

    N

    INT(bitWidth=8, isSigned=true)

    Y

    Y

    Y

    N

    N

    N

    INT(bitWidth=16, isSigned=false)

    D

    Y

    Y

    N

    N

    N

    INT(bitWidth=16, isSigned=true)

    Y

    Y

    Y

    N

    N

    N

    INT(bitWidth=32, isSigned=false)

    D

    D

    Y

    N

    N

    N

    INT(bitWidth=32, isSigned=true)

    D

    Y

    Y

    N

    N

    N

    INT(bitWidth=64, isSigned=false)

    D

    D

    D

    N

    N

    N

    INT(bitWidth=64, isSigned=true)

    D

    D

    Y

    N

    N

    N

    DECIMAL(precision=m, scale=n)

    N

    N

    N

    D

    N

    N

    FLOAT16

    N

    N

    N

    N

    Y

    Y

    FLOAT

    N

    N

    N

    N

    Y

    Y

    DOUBLE

    N

    N

    N

    N

    N

    Y

    STRING

    N

    N

    N

    N

    N

    N

    BYTE_ARRAY

    N

    N

    N

    N

    N

    N

    FIXED_LEN_BYTE_ARRAY

    N

    N

    N

    N

    N

    N

    DATE

    N

    N

    N

    N

    N

    N

    TIME(timeUnit=milliseconds)

    N

    N

    N

    N

    N

    N

    TIME(timeUnit=microseconds)

    N

    N

    N

    N

    N

    N

    TIME(timeUnit=nanoseconds)

    N

    N

    N

    N

    N

    N

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=false)

    N

    N

    N

    N

    N

    N

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=false)

    N

    N

    N

    N

    N

    N

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=false)

    N

    N

    N

    N

    N

    N

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=true)

    N

    N

    N

    N

    N

    N

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=true)

    N

    N

    N

    N

    N

    N

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=true)

    N

    N

    N

    N

    N

    N

    BOOLEAN

    N

    N

    N

    N

    N

    N

    UUID

    N

    N

    N

    N

    N

    N

    LIST

    N

    N

    N

    N

    N

    N

    STRUCT

    N

    N

    N

    N

    N

    N

    MAP

    N

    N

    N

    N

    N

    N

    Other than above

    N

    N

    N

    N

    N

    N
    Legend:

    Y: Supported.

    D: Supported. However, if the data type cannot be converted to the HADB data type due to overflow or other reasons, the SQL statement will result in an error. If the field data type of each field of STRUCT type and the element data type of each array element of array type cannot also be converted to the HADB data type, the SQL statement will result in an error.

    N: Not supported.

    #1

    If the integer data type format type is a legacy format, the correspondence is the same as for the INTEGER type.

    #2

    If the integer data type format type is a legacy format, the correspondence is the same as for the BIGINT type.

    Table 3‒7: Correspondence between the data type of each column of foreign data in PARQUET format and the HADB data type (part 2)

    Data types of foreign data columns in PARQUET format

    Corresponding HADB data types

    Character string data

    DATE

    TIME

    TIMESTAMP WITHOUT TIME ZONE

    TIMESTAMP WITH TIME ZONE

    Binary data

    ARRAY

    NULL

    Y

    Y

    Y

    Y

    Y

    Y

    D#1

    INT(bitWidth=8, isSigned=false)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=8, isSigned=true)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=16, isSigned=false)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=16, isSigned=true)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=32, isSigned=false)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=32, isSigned=true)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=64, isSigned=false)

    N

    N

    N

    N

    N

    N

    N

    INT(bitWidth=64, isSigned=true)

    N

    N

    N

    N

    N

    N

    N

    DECIMAL(precision=m, scale=n)

    N

    N

    N

    N

    N

    N

    N

    FLOAT16

    N

    N

    N

    N

    N

    N

    N

    FLOAT

    N

    N

    N

    N

    N

    N

    N

    DOUBLE

    N

    N

    N

    N

    N

    N

    N

    STRING

    D

    N

    N

    N

    N

    N

    N

    BYTE_ARRAY

    N

    N

    N

    N

    N

    D

    N

    FIXED_LEN_BYTE_ARRAY

    N

    N

    N

    N

    N

    D

    N

    DATE

    N

    Y

    N

    N

    N

    N

    N

    TIME(timeUnit=milliseconds)

    N

    N

    D

    N

    N

    N

    N

    TIME(timeUnit=microseconds)

    N

    N

    D

    N

    N

    N

    N

    TIME(timeUnit=nanoseconds)

    N

    N

    D

    N

    N

    N

    N

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=false)

    N

    N

    N

    D

    N

    N

    N

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=false)

    N

    N

    N

    D

    N

    N

    N

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=false)

    N

    N

    N

    D

    N

    N

    N

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=true)

    N

    N

    N

    N

    D

    N

    N

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=true)

    N

    N

    N

    N

    D

    N

    N

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=true)

    N

    N

    N

    N

    D

    N

    N

    BOOLEAN

    N

    N

    N

    N

    N

    N

    N

    UUID

    N

    N

    N

    N

    N

    N

    N

    LIST

    N

    N

    N

    N

    N

    N

    D

    STRUCT

    N

    N

    N

    N

    N

    N

    N

    MAP

    N

    N

    N

    N

    N

    N

    D#2

    Other than above

    N

    N

    N

    N

    N

    N

    N
    Legend:

    Y: Supported.

    D: Supported. However, if the data type cannot be converted to the HADB data type due to overflow or other reasons, the SQL statement will result in an error. If the field data type of each field of STRUCT type and the element data type of each array element of array type cannot also be converted to the HADB data type, the SQL statement will result in an error.

    N: Not supported.

    Character string data: CHARACTER, VARCHAR, STRING

    Binary data: BINARY, VARBINARY

    #1

    An ARRAY-type column with STRUCT type as its element data type is not supported.

    #2

    If the column definition of the foreign table is omitted, the data type of the foreign table column corresponding to the MAP type is defined as the following array type. Only this array type is supported.

    STRUCT{"key"   data-type-of-MAP-key,
       "value" data-type-of-MAP-value} ARRAY[30000]
    Table 3‒8: Correspondence between the data type of each column of foreign data in PARQUET format and the HADB data type (part 3)

    Data types of foreign data columns in PARQUET format

    Corresponding HADB data types

    STRUCT

    BOOLEAN

    UUID

    NULL

    N

    Y

    Y

    INT(bitWidth=8, isSigned=false)

    N

    N

    N

    INT(bitWidth=8, isSigned=true)

    N

    N

    N

    INT(bitWidth=16, isSigned=false)

    N

    N

    N

    INT(bitWidth=16, isSigned=true)

    N

    N

    N

    INT(bitWidth=32, isSigned=false)

    N

    N

    N

    INT(bitWidth=32, isSigned=true)

    N

    N

    N

    INT(bitWidth=64, isSigned=false)

    N

    N

    N

    INT(bitWidth=64, isSigned=true)

    N

    N

    N

    DECIMAL(precision=m, scale=n)

    N

    N

    N

    FLOAT16

    N

    N

    N

    FLOAT

    N

    N

    N

    DOUBLE

    N

    N

    N

    STRING

    N

    N

    N

    BYTE_ARRAY

    N

    N

    N

    FIXED_LEN_BYTE_ARRAY

    N

    N

    N

    DATE

    N

    N

    N

    TIME(timeUnit=milliseconds)

    N

    N

    N

    TIME(timeUnit=microseconds)

    N

    N

    N

    TIME(timeUnit=nanoseconds)

    N

    N

    N

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=false)

    N

    N

    N

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=false)

    N

    N

    N

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=false)

    N

    N

    N

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=true)

    N

    N

    N

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=true)

    N

    N

    N

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=true)

    N

    N

    N

    BOOLEAN

    N

    Y

    N

    UUID

    N

    N

    Y

    LIST

    N

    N

    N

    STRUCT

    D

    N

    N

    MAP

    N

    N

    N

    Other than above

    N

    N

    N
    Legend:

    Y: Supported.

    D: Supported. However, if the data type cannot be converted to the HADB data type due to overflow or other reasons, the SQL statement will result in an error. If the field data type of each field of STRUCT type and the element data type of each array element of array type cannot also be converted to the HADB data type, the SQL statement will result in an error.

    N: Not supported.

  8. When loading foreign data in CSV or JSON format into a foreign table, use the libraries for accessing foreign data. Load data into a foreign table that has been converted to the Apache Arrow data type corresponding to the HADB data type specified in the column definition of the foreign table. The following table shows the correspondence between HADB data types and Apache Arrow data types.

    Table 3‒9: Correspondence between HADB data types and Apache Arrow data types

    HADB data type specified in the column definition of the foreign table

    Corresponding Apache Arrow data types

    SMALLINT#1

    INT16

    INTEGER#2

    INT32

    BIGINT

    INT64

    DECIMAL(m,n)

    DECIMAL128(m,n)

    NUMERIC(m,n)

    REAL

    FLOAT32

    FLOAT

    FLOAT64

    DOUBLE PRECISION

    CHAR

    STRING

    VARCHAR

    STRING

    DATE

    DATE32

    TIME(0)

    TIME32(TimeUnit:SECOND)

    TIME(3)

    TIME32(TimeUnit:MILLI)

    TIME(6)

    TIME64(TimeUnit:MICRO)

    TIME(9)

    TIME64(TimeUnit:NANO)

    TIMESTAMP(0) WITHOUT TIME ZONE

    TIMESTAMP(TimeUnit:SECOND, isAdjustedToUTC=false)

    TIMESTAMP(3) WITHOUT TIME ZONE

    TIMESTAMP(TimeUnit:MILLI, isAdjustedToUTC=false)

    TIMESTAMP(6) WITHOUT TIME ZONE

    TIMESTAMP(TimeUnit:MICRO, isAdjustedToUTC=false)

    TIMESTAMP(9) WITHOUT TIME ZONE

    TIMESTAMP(TimeUnit:NANO, isAdjustedToUTC=false)

    TIMESTAMP(0) WITH TIME ZONE

    TIMESTAMP(TimeUnit:SECOND, isAdjustedToUTC=true)

    TIMESTAMP(3) WITH TIME ZONE

    TIMESTAMP(TimeUnit:MILLI, isAdjustedToUTC=true)

    TIMESTAMP(6) WITH TIME ZONE

    TIMESTAMP(TimeUnit:MICRO, isAdjustedToUTC=true)

    TIMESTAMP(9) WITH TIME ZONE

    TIMESTAMP(TimeUnit:NANO, isAdjustedToUTC=true)

    BINARY

    BINARY

    VARBINARY

    BOOLEAN

    BOOL

    ARRAY

    LIST#3

    STRUCT

    STRUCT#4
    Note

    For more information on Apache Arrow data types, see Data Types in the Apache Arrow documentation.

    #1

    If the integer data type format is in a legacy format, the corresponding Apache Arrow data type is INT32.

    #2

    If the integer data type format is in a legacy format, the corresponding Apache Arrow data type is INT64.

    #3

    The rules in the table above also apply to the element data types of array elements.

    #4

    The rules in the table above also apply to the field data type of the field.

    Note

    When loading foreign data, SQL statements will result in an error if they contain data that cannot be converted to Apache Arrow data types. For example, an error occurs in the following cases.

    • If the foreign data contains data that exceeds the range of the Apache Arrow data types (the range of HADB data types specified in the column definitions of the foreign table)

    • If the foreign data contains data that is incompatible with the Apache Arrow data type corresponding to the HADB data type specified in the column definition of the foreign table (e.g., character data in the foreign data when the data type of the column definition of the foreign table is numeric data)

  9. If the foreign data is in CSV format, prepare foreign data that corresponds to the following rules.

    • One row of foreign data becomes one row of data in the foreign table.

    • Use a delimiting character to separate the field data from the field data of the foreign data.

    • Enclosing characters can be specified at both ends of the field data of the foreign data.

    • If the row is blank, null values are set in all columns of the foreign table.

    • The length of a row of foreign data should be 64 megabytes or less.

    • Only the beginning and end of each field data of the foreign data can be specified with spaces and tabs (cannot specify other than the beginning and end). However, spaces as part of a character string or binary data or as a time stamp notation is acceptable.

    • It is recommended that the format of each field data in the foreign data correspond to the HADB data type specified in the column definition of the foreign table. The following table shows the correspondence.

    Table 3‒10: Specification format of field data corresponding to the HADB data type defined in the foreign table

    HADB data type specified for column definition in foreign table

    Recommended field data specification format

    Specification example

    Description and notes

    SMALLINT

    Integer

    • 123

    • -123

    • Plus sign (+) cannot be specified. For example, +123 cannot be specified.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    INTEGER

    BIGINT

    DECIMAL(m,n)

    Decimal

    • 123.45

    • -123.45

    • +123

    • .12

    If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    NUMERIC(m,n)

    REAL

    Floating point number

    • 1.234E5

    • 1.234E+5

    • 1.234E-5

    • When displaying the exponent, the exponent cannot be omitted. For example, 123E cannot be specified.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    FLOAT

    DOUBLE PRECISION

    CHAR

    Character-string

    ABC

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    • If there is a character in the field data that is the same as the enclosing character, specify two consecutive characters that are the same as the enclosing character.

    • When specifying an empty character when the data type of the column is VARCHAR or STRING, set the field data as an empty character and specify the enclosing character.

    VARCHAR

    STRING

    DATE

    Date

    2025-03-01

    • Specify the date in the format YYYY-MM-DD. YYYY means year, MM means month, and DD means day.

    • The / character cannot be used to delimit the year, month, and day. For example, 2025/03/01 cannot be specified.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    TIME(0)

    Time

    • 11:22:33

    • 11:22:33.123

    • 11:22:33.123456

    • 11:22:33.123456789

    • Specify the time in the format hh:mm:ss.nn...n. hh means hours, mm means minutes, and ss means seconds. If the fractional seconds precision is 1 or greater, it is specified as .nn...nn.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    TIME(3)

    TIME(6)

    TIME(9)

    TIMESTAMP(0) WITHOUT TIME ZONE

    Time stamp (without time zone)

    • 2020-06-01 11:22:33

    • 2020-06-01 11:22:33.123

    • 2020-06-01 11:22:33.123456

    • 2020-06-01 11:22:33.123456789

    • Specify the time stamp without time zone in the format YYYY-MM-DD hh:mm:ss.nn...n. YYYY means year, MM means month, DD means day, hh means hour, mm means minute, and ss means second. If the fractional seconds precision is 1 or greater, it is specified as .nn...nn.

      Also, insert a half-width space after DD.

    • The / character cannot be used to delimit the year, month, and day. For example, 2025/03/01 12:34:56 cannot be specified.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    • When specifying TIMESTAMP(9) WITHOUT TIME ZONE in the column definition of a foreign table, specify values in the range of 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807. If values outside this range are specified, invalid values will be returned.

    TIMESTAMP(3) WITHOUT TIME ZONE

    TIMESTAMP(6) WITHOUT TIME ZONE

    TIMESTAMP(9) WITHOUT TIME ZONE

    TIMESTAMP(0) WITH TIME ZONE

    Time stamp (with time zone)

    • 2025-06-01 11:22:33+00:00

    • 2025-06-01 20:22:33+09:00

    • 2025-06-01 11:22:33Z

    • 2025-06-01 11:22:33.123+00:00

    • 2025-06-01 11:22:33.123456+00:00

    • 2025-06-01 11:22:33.123456789+00:00

    • Specify the time stamp with time zone in the format YYYY-MM-DD hh:mm:ss.nn...n±hh:mm. YYYY means year, MM means month, DD means day, hh means hour, mm means minute, and ss means second. If the fractional seconds precision is 1 or greater, it is specified as .nn...nn.

      Also, insert a half-width space after DD.

      Specify the time zone in the format ±hh:mm. If the digits for hours (hh) or minutes (mm) are insufficient, pad with zeros on the left. In addition, you can specify Z instead of +00:00.

    • The / character cannot be used to delimit the year, month, and day. For example, 2025/03/01 12:34:56+00:00 cannot be specified.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    • When specifying TIMESTAMP(9) WITH TIME ZONE in the column definition of a foreign table, specify values in the range of 1677-09-21 00:12:43.145224192+00:00 to 2262-04-11 23:47:16.854775807+00:00. If values outside this range are specified, invalid values will be returned.

    TIMESTAMP(3) WITH TIME ZONE

    TIMESTAMP(6) WITH TIME ZONE

    TIMESTAMP(9) WITH TIME ZONE

    BINARY

    Character-string

    ABCD

    • Loads the specified data as it is into a foreign table as binary data. For example, if ABCD is specified as the character string, it is loaded in the foreign table as 0x41424344.

    • Data cannot be represented by hexadecimal and binary character strings.

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    • If there is a character in the field data that is the same as the enclosing character, specify two consecutive characters that are the same as the enclosing character.

    • When specifying an empty character when the data type of the column is VARBINARY, set the field data as an empty character and specify the enclosing character.

    VARBINARY

    BOOLEAN

    Boolean value

    • True

    • False

    • true

    • false

    • TRUE

    • FALSE

    • If you want to set a null value, set the field data as empty characters and do not specify enclosing characters.

    • Formats other than the specified examples are not recognized as BOOLEAN type.

  10. If the foreign data is in JSON format, prepare foreign data that corresponds to the following rules.

    • The recommended description format for JSON files is Line-separated JSON (JSONL), in which each JSON object is declared by delimiting it with a new line.

    • Declare one line of data in a table in a single JSON object delimited by new lines.

    • Each JSON object should be declared delimited by a new line. Do not separate them with commas (,).

    • Each JSON object should be enclosed in curly brackets ({ }).

    • A JSON object value cannot contain spaces that does not conform to the JSON specification.

    • If you want to set a null value as the value of a JSON object, write null for the value. Do not enclose null in double quotation marks (").

    • The length of a line in a JSON file should be 64 megabytes or less.

    • A JSON object consists of key/value pairs and is expressed in the following description format.

      [key:value [,key:value]...]

      One key/value pair corresponds to one column of data in a table. The key is the name of the corresponding column and the value is the value corresponding to the data type of the column.

      Note that even if some or all rows in the JSON file do not contain key/value pairs corresponding to column definitions of the foreign table, the SQL statement will not result in an error. A null value is set for keys that do not exist in the row.

      The following table shows the recommended format for specifying keys and values.

      Table 3‒11: Recommended format for specifying keys and values

      HADB data type specified for column definition in foreign table

      Recommended format for specifying keys and values

      Specification example

      Description and notes

      SMALLINT

      "column-name":integer

      • "C1":123

      • "C1":-123

      • Double quotation marks cannot be used to enclose integers. For example, "C1":"123" cannot be specified.

      • Plus sign (+) cannot be specified. For example, "C1":+123 cannot be specified.

      • Unnecessary zeros cannot be added to the beginning of an integer. For example, "C1":0123 cannot be specified.

      • To set a null value, specify "column-name":null.

      INTEGER

      BIGINT

      DECIMAL(m,n)

      "column-name":decimal-number

      • "C1":123.45

      • "C1":-123.45

      • Double quotation marks cannot be used to enclose decimal numbers. For example, "C1":"123.45" cannot be specified.

      • Plus sign (+) cannot be specified. For example, "C1":+123.45 cannot be specified.

      • Unnecessary zeros cannot be added to the beginning of an decimal number. For example, "C1":0123.45 cannot be specified.

      • The integer of the decimal cannot be omitted. For example, "C1":.12 cannot be specified.

      • To set a null value, specify "column-name":null.

      NUMERIC(m,n)

      REAL

      "column-name":floating-point-number

      • "C1":1.234E5

      • "C1":1.234E+5

      • "C1":1.234E-5

      • Double quotation marks cannot be used to enclose floating point numbers. For example, "C1":"1234E5" cannot be specified.

      • Plus sign (+) cannot be specified. For example, "C1":+1234E5 cannot be specified.

      • Unnecessary zeros cannot be added to the beginning of floating-point numbers. For example, "C1":01234E5 cannot be specified.

      • The integer part of a floating-point number cannot be omitted. For example, "C1":E5 cannot be specified.

      • When displaying the exponent, the exponent cannot be omitted. For example, "C1":123E cannot be specified.

      • To set a null value, specify "column-name":null.

      FLOAT

      DOUBLE PRECISION

      CHAR

      "column-name":"character-string"

      "C1":"ABC"

      • Character strings that are not enclosed in double quotation marks cannot be read.

      • Control characters such as a new line (\n), tab (\t) , backslash (\\), etc., must be escaped. For example, if "C1":"A\nB" is specified, \n means a new line.

      • To set a null value, specify "column-name":null.

      VARCHAR

      STRING

      DATE

      "column-name":integer

      "C1":18414

      • Double quotation marks cannot be used to enclose integers. For example, "C1":"123" cannot be specified.

      • Plus sign (+) cannot be specified. For example, "C1":+123 cannot be specified.

      • Unnecessary zeros cannot be added to the beginning of an integer. For example, "C1":0123 cannot be specified.

      • The integer specifies the ordinal day with UNIX epoch (1970/01/01) as 0. For example, if "C1":18414 is specified, the ordinal day will be "2020-06-01".

      • Character string expressions such as "C1":"2025-03-01" cannot be specified.

      • To set a null value, specify "column-name":null.

      TIME(0)

      "column-name":integer

      "C1":45296789

      • Double quotation marks cannot be used to enclose integers. For example, "C1":"123" cannot be specified.

      • Plus sign (+) cannot be specified. For example, "C1":+123 cannot be specified.

      • Unnecessary zeros cannot be added to the beginning of an integer. For example, "C1":0123 cannot be specified.

      • For integers, 00:00:00 is set to 0, and the total number of seconds, milliseconds, microseconds, and nanoseconds are specified. For example, if the data type of HADB is TIME(3) and "C1":45296789 is specified, the result will be "12:34:56.789".

      • Character string expressions such as "C1":"12:34:56.789" cannot be specified.

      • To set a null value, specify "column-name":null.

      TIME(3)

      TIME(6)

      TIME(9)

      TIMESTAMP(0) WITHOUT TIME ZONE

      "column-name":"time-stamp-without-time-zone"

      • "C1":"2020-06-01 11:22:33"

      • "C1":"2020-06-01 11:22:33.123"

      • "C1":"2020-06-01 11:22:33.123456"

      • "C1":"2020-06-01 11:22:33.123456789"

      • Specify the time stamp without time zone in the format YYYY-MM-DD hh:mm:ss.nn...n. YYYY means year, MM means month, DD means day, hh means hour, mm means minute, and ss means second. If the fractional seconds precision is 1 or greater, it is specified as .nn...nn.

        Also, insert a half-width space after DD.

      • The value of a time stamp cannot be loaded when it is not enclosed in double quotation marks.

      • The / character cannot be used to delimit the year, month, and day. For example, 2025/03/01 12:34:56 cannot be specified.

      • To set a null value, specify "column-name":null.

      • When specifying TIMESTAMP(9) WITHOUT TIME ZONE in the column definition of a foreign table, specify values in the range of 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807. If values outside this range are specified, invalid values will be returned.

      TIMESTAMP(3) WITHOUT TIME ZONE

      TIMESTAMP(6) WITHOUT TIME ZONE

      TIMESTAMP(9) WITHOUT TIME ZONE

      TIMESTAMP(0) WITH TIME ZONE

      "column-name":"time-stamp-with-time-zone"

      • "C1":"2025-06-01 11:22:33+00:00"

      • "C1":"2025-06-01 20:22:33+09:00"

      • "C1":"2025-06-01 11:22:33Z"

      • "C1":"2025-06-01 11:22:33.123+00:00"

      • "C1":"2025-06-01 11:22:33.123456+00:00"

      • "C1":"2025-06-01 11:22:33.123456789+00:00"

      • Specify the time stamp with time zone in the format YYYY-MM-DD hh:mm:ss.nn...n±hh:mm. YYYY means year, MM means month, DD means day, hh means hour, mm means minute, and ss means second. If the fractional seconds precision is 1 or greater, it is specified as .nn...nn.

        Also, insert a half-width space after DD.

        Specify the time zone in the format ±hh:mm. If the digits for hours (hh) or minutes (mm) are insufficient, pad with zeros on the left. In addition, you can specify Z instead of +00:00.

      • The value of a time stamp cannot be loaded when it is not enclosed in double quotation marks.

      • The / character cannot be used to delimit the year, month, and day. For example, 2025/03/01 12:34:56+00:00 cannot be specified.

      • To set a null value, specify "column-name":null.

      • When specifying TIMESTAMP(9) WITH TIME ZONE in the column definition of a foreign table, specify values in the range of 1677-09-21 00:12:43.145224192+00:00 to 2262-04-11 23:47:16.854775807+00:00. If values outside this range are specified, invalid values will be returned.

      TIMESTAMP(3) WITH TIME ZONE

      TIMESTAMP(6) WITH TIME ZONE

      TIMESTAMP(9) WITH TIME ZONE

      BINARY

      "column-name":"character-string"

      "C1":"ABCD"

      • Character strings that are not enclosed in double quotation marks cannot be read.

      • Loads the specified data as it is into a foreign table as binary data. For example, if ABCD is specified as the character string, it is loaded in the foreign table as 0x41424344.

      • Data cannot be represented by hexadecimal and binary character strings.

      • Control characters such as a new line (\n), tab (\t) , backslash (\\), etc., must be escaped. For example, if "C1":"A\nB" is specified, \n means a new line.

      • To set a null value, specify "column-name":null.

      VARBINARY

      BOOLEAN

      "column-name":boolean-value

      • "C1":true

      • "C1":false

      • Double quotation marks cannot be used to enclose boolean values. For example, "C1":"true" cannot be specified.

      • Uppercase letters cannot be used for boolean values. For example, "C1":True cannot be specified.

      • To set a null value, specify "column-name":null.

      ARRAY

      "column-name":[array-element-value[,array-element-value]…]#

      "C1":["A","B","C"]

      • For the array element value, specify the value corresponding to the element data type according to the data type specification format described in this table.

      • To set a null value, specify "column-name":null.

      • To set a null value to an array element value, specify null for the array element value.

      STRUCT

      "column-name":{field-name:field-value[,field-name:field-value]…}

      "C1":{"F1":"X", "F2":"Y", "F3":"Z"}

      • The field name specifies the name of the field of the structure data.

      • For the field value, specify the value corresponding to the field data type according to the data type specification format described in this table.

      • To set a null value, specify "column-name":null.

      • If you want to set a null value for the field value, specify null for the field value.
    #:

    [Figure]

To Page Top

(5) Rules for omitting the column definition specification

When the column definition specification is omitted, column definitions are generated according to the rules described here.

(a) If ICEBERG is specified in the foreign data format specification

  • The HADB server automatically generates the column definitions (column names and column data types) of the foreign table based on the metadata of the referenced Iceberg table. All columns included in the metadata of the Iceberg table are defined as the column definitions of the foreign table.

  • If the number of generated column definitions is 4,001 or more, the CREATE FOREIGN TABLE statement will result in an error.

  • If the data type of a generated column corresponds to any of the following, the CREATE FOREIGN TABLE statement will result in an error.

    • TIME type other than TIME(6)

    • TIMESTAMP WITHOUT TIME ZONE type other than TIMESTAMP(6) WITHOUT TIME ZONE

    • TIMESTAMP WITH TIME ZONE type other than TIMESTAMP(6) WITH TIME ZONE

    • STRUCT type with any of the above data types specified as a field data type, or ARRAY type with any of the above data types specified as the element data type

  • The column names of the column definitions generated by the HADB server are the same as the column names included in the metadata of the referenced Iceberg table. The column names included in the metadata of the Iceberg table must satisfy all of the following conditions. If they do not satisfy these conditions, the CREATE FOREIGN TABLE statement will result in an error.

  • The data types of the column definitions generated by the HADB server are determined based on the data types of the columns included in the metadata of the referenced Iceberg table. The correspondence between the data types of the columns in the Iceberg table metadata and the HADB data types is shown in the following table.

    Table 3‒12: Correspondence between the data types of the columns included in the metadata of the Iceberg table and the HADB data types

    Data types of the columns included in the metadata of the Iceberg table

    Corresponding HADB data types (data types for columns in foreign tables)

    int

    INTEGER#1

    long

    BIGINT

    decimal (precision=m, scale=n)

    DECIMAL(m,n)#2

    float

    REAL

    double

    DOUBLE

    string

    STRING

    binary

    VARBINARY(32000)#3

    fixed(L)

    BINARY(L)#4

    date

    DATE

    time

    TIME(6)

    timestamp

    TIMESTAMP(6) WITHOUT TIME ZONE

    timestamptz

    TIMESTAMP(6) WITH TIME ZONE

    boolean

    BOOLEAN

    uuid

    UUID

    list

    ARRAY[30000]#5

    struct

    STRUCT#6

    map

    An ARRAY type with STRUCT type elements#7

    Other than above

    There is no corresponding HADB data type.
    #1

    If the integer data type format is in legacy format, the corresponding HADB data type will be SMALLINT.

    #2

    If 1 ≤ m ≤ 38, 0 ≤ n ≤ 38, and n ≤ m are not satisfied, the CREATE FOREIGN TABLE statement will result in an error.

    #3

    If the data type cannot be converted to the HADB data type due to overflow or other reasons, the SQL statement will result in an error when the foreign table is searched.

    #4

    If the data length of the binary data is not between 1 and 32,000, the CREATE FOREIGN TABLE statement will result in an error.

    #5

    • If a data type other than numeric, character string, datetime, binary, logical, UUID, or structure data is specified for the element data type, the CREATE FOREIGN TABLE statement will result in an error.

    • If the repetition count of both nested structure data and array data exceeds 8, the CREATE FOREIGN TABLE statement will result in an error.

    • The maximum number of elements for array data must be between 2 and 30,000.

    #6

    • If the maximum number of fields in the structure data exceeds 1,000, the CREATE FOREIGN TABLE statement will result in an error. Additionally, if structure data is specified within structure data, and the total number of fields in all structure data exceeds 1,000, the CREATE FOREIGN TABLE statement will also result in an error.

    • If the field data type specifies nested structure or array data, and the repetition count of either exceeds 8, the CREATE FOREIGN TABLE statement will result in an error.

    • Duplicate names cannot be specified for the field names in structure data.

    • For rules for specifying field names in structure data, see (2) Rules for characters that can be used in names in 6.1.4 Specifying names.

    #7

    In HADB, this is treated as an ARRAY type whose element data type is STRUCT type, as shown below. In this case, the rules for ARRAY type and STRUCT type in the table above apply.

    STRUCT{"key" data-type-of-map-key, "value" data-type-of-map-value} ARRAY[30000]

(b) If PARQUET is specified in the foreign data format specification

  • If the column definition is omitted, the HADB server automatically generates the column definitions by referencing the PARQUET format foreign data files in the directory specified for foreign data search (including subdirectories), and automatically determines the column names and data types of the foreign table columns.

  • If the HADB server generates column definitions, the column names will be the same as those in the schema information of the PARQUET format foreign data files. Column definitions will be generated for all columns that exist in the schema information.

    Important

    The column names in the schema information must meet all of the following conditions. Otherwise, the CREATE FOREIGN TABLE statement will result in an error.

  • If the HADB server generates column definitions, the data types of the column definitions will be determined by converting the data types of the columns in the schema information of the PARQUET format foreign data files to Apache Arrow data types, and then converting those to HADB data types. The correspondence between the data types of the columns in the PARQUET format foreign data and the HADB data types is shown in the following table.

    Table 3‒13: Correspondence between the data types of the columns in the PARQUET format foreign data and the HADB data types

    Data types of foreign data columns in PARQUET format

    Corresponding HADB data types (data types for columns in foreign tables)

    NULL

    BIGINT

    INT(bitWidth=8, isSigned=false)

    SMALLINT

    INT(bitWidth=8, isSigned=true)

    INT(bitWidth=16, isSigned=false)

    INTEGER#1

    INT(bitWidth=16, isSigned=true)

    SMALLINT

    INT(bitWidth=32, isSigned=false)

    BIGINT

    INT(bitWidth=32, isSigned=true)

    INTEGER#1

    INT(bitWidth=64, isSigned=false)

    BIGINT#2, #3

    INT(bitWidth=64, isSigned=true)

    BIGINT

    DECIMAL(precision=m, scale=n)

    DECIMAL(m,n)#4

    FLOAT16

    REAL

    FLOAT

    DOUBLE

    DOUBLE PRECISION

    STRING

    STRING#3

    BYTE_ARRAY

    VARBINARY(32000)#3

    FIXED_LEN_BYTE_ARRAY

    BINARY(n)#5

    DATE

    DATE

    TIME(timeUnit=milliseconds)

    TIME(3)

    TIME(timeUnit=microseconds)

    TIME(6)

    TIME(timeUnit=nanoseconds)

    TIME(9)

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=false)

    TIMESTAMP(3) WITHOUT TIME ZONE

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=false)

    TIMESTAMP(6) WITHOUT TIME ZONE

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=false)

    TIMESTAMP(9) WITHOUT TIME ZONE

    TIMESTAMP(timeUnit=milliseconds, isAdjustedToUTC=true)

    TIMESTAMP(3) WITH TIME ZONE

    TIMESTAMP(timeUnit=microseconds, isAdjustedToUTC=true)

    TIMESTAMP(6) WITH TIME ZONE

    TIMESTAMP(timeUnit=nanoseconds, isAdjustedToUTC=true)

    TIMESTAMP(9) WITH TIME ZONE

    BOOLEAN

    BOOLEAN

    UUID

    UUID

    LIST

    ARRAY#6

    STRUCT

    STRUCT#7

    MAP

    An ARRAY type with STRUCT type elements#8

    Other than above

    There is no corresponding HADB data type.
    #1

    If the integer data type format is in legacy format, the corresponding HADB data type will be SMALLINT.

    #2

    If the integer data type format is in legacy format, the corresponding HADB data type will be INTEGER.

    #3

    If the data type cannot be converted to the HADB data type due to overflow or other reasons, the SQL statement will result in an error when the foreign table is searched.

    #4

    If 1 ≤ m ≤ 38, 0 ≤ n ≤ 38, and n ≤ m are not satisfied, the CREATE FOREIGN TABLE statement will result in an error.

    #5

    If the data length of the binary data is not between 1 and 32,000, the CREATE FOREIGN TABLE statement will result in an error.

    #6

    • If a data type other than numeric, character string, datetime, binary, logical, UUID, or structure data is specified for the element data type, the CREATE FOREIGN TABLE statement will result in an error.

    • If the repetition count of both nested structure data and array data exceeds 8, the CREATE FOREIGN TABLE statement will result in an error.

    • The maximum number of elements for array data must be between 2 and 30,000.

    • If the maximum number of elements (n) can be obtained from the PARQUET format foreign data, the element data type will be an ARRAY type with a maximum number of n elements. If the maximum number of elements cannot be obtained, the element data type will be an ARRAY type with a maximum number of 30,000 elements.

    #7

    • If the maximum number of fields in the structure data exceeds 1,000, the CREATE FOREIGN TABLE statement will result in an error. Additionally, if structure data is specified within structure data, and the total number of fields in all structure data exceeds 1,000, the CREATE FOREIGN TABLE statement will also result in an error.

    • If the field data type specifies nested structure or array data, and the repetition count of either exceeds 8, the CREATE FOREIGN TABLE statement will result in an error.

    • Duplicate names cannot be specified for the field names in structure data.

    • For rules for specifying field names in structure data, see (2) Rules for characters that can be used in names in 6.1.4 Specifying names.

    #8

    In HADB, this is treated as an ARRAY type whose element data type is STRUCT type, as shown below. In this case, the rules for ARRAY type and STRUCT type in the table above apply.

    STRUCT{"key" data-type-of-MAP-key, "value" data-type-of-MAP-value} ARRAY[30000]

  • If any of the following conditions are met, the CREATE FOREIGN TABLE statement will result in an error.

    • If the total number of generated column definitions and the number of partition key columns is 4,001 or more.

    • If no PARQUET format foreign data files are stored under the target directory for foreign data search (including subdirectories).

    • If the libraries for accessing foreign data cannot read the PARQUET format foreign data stored under the target directory for foreign data search (including subdirectories).

Important

Ensure that the column names and data types present in the schema information of all PARQUET-format foreign data files under the target directory for foreign data search (including subdirectories) are identical.

To Page Top

(6) Notes

The following describes notes when ICEBERG is specified in the foreign data format specification.

  1. All foreign data to be searched must be stored in files in PARQUET format.

  2. Iceberg tables that have generated delete files due to row-level updates cannot be searched.

  3. When retrieving a foreign table, the column definitions of the foreign table (such as column names, column data types) are generated based on the metadata of the Iceberg table. At this time, the column definitions of the foreign table are generated according to the rules described in (a) If ICEBERG is specified in the foreign data format specification in (5) Rules for omitting the column definition specification.

    In addition, if time travel specification is specified when retrieving the foreign table (when referencing past metadata), the column definitions of the foreign table (such as column names, column data types) are generated based on the metadata of the snapshot specified in the time travel specification.

  4. The information about the column definitions of the foreign table stored in the dictionary tables (SQL_TABLES table, SQL_COLUMNS table, and SQL_DATATYPE_DESCRIPTORS table) is from the time when the foreign table was defined. It is not from the time when the foreign table is retrieved. Therefore, if you change the information about column definitions included in the metadata, the information about the column definitions of the foreign table stored in the dictionary tables will not match the information about the column definitions of the foreign table used when retrieving the foreign table. If you want them to match, delete the foreign table once and then redefine the foreign table.

    However, if time travel specification is specified when retrieving the foreign table, the information will not match the information stored in the dictionary tables even if you redefine the foreign table.

  5. When retrieving a view table that has a foreign table as an underlying table, the column definitions of the foreign table are generated based on the metadata at the time the view table is retrieved. If the column definitions of the foreign table differ between when the view table was defined and when the view table is retrieved (if the information about column definitions included in the metadata was changed after the view table was defined), the SQL statement will result in an error when retrieving the view table. In this case, recreate the view table using the ALTER VIEW statement or similar.

    Note that for a view table defined with time travel specification for the foreign table, the column definitions of the foreign table do not differ between when the view table was defined and when the view table is retrieved (because the same snapshot metadata is referenced), so the SQL statement will not result in an error when retrieving the view table.

  6. Foreign tables defined with ICEBERG specified as the foreign data format specification support Iceberg's schema evolution. However, it is necessary to ensure that the definition information for each data file can be uniquely identified from the metadata of the Iceberg table and the metadata of the data files. If the definition information of data files is registered in the Iceberg table without setting metadata in the data files, the HADB server will not be able to identify the definition information of the data files when schema evolution is performed. As a result, SQL statements might result in errors when retrieving the foreign table. If an error occurs, regenerate the data files for the Iceberg table.

  7. When retrieving a foreign table, the column definitions (such as column names and data types) of the foreign table are generated from the metadata of the Iceberg table. Therefore, if Iceberg's schema evolution is performed during the retrieval of the foreign table, a mismatch in the column definitions of the foreign table might cause errors in retrieving the foreign table.

To Page Top

(7) Examples

■If a partition key column is not specified

The following is an example of defining a foreign table without specifying a partition key column.

Suppose the structure of the foreign data file that contains the foreign data is as follows.

[Figure]

Example 1:

Define a foreign table (FT1).

CREATE FOREIGN TABLE "FT1" ("C1" INTEGER,"C2" INTEGER)   ...1
  SERVER "SERVER1"                                       ...2
  OPTIONS (                                              ...3
            FORMAT CSV                                   ...4
           ,LOCATION 's3://data/'                        ...5
          )

Explanation:

  1. Specifies the table name of the foreign table and the column definitions of the foreign table.

  2. Specifies the name of the foreign server used by the foreign table.

  3. After OPTIONS, foreign table options are specified.

  4. Specifies the data format of the foreign data. Specifies CSV because the foreign data is in CSV format.

  5. Specifies the path for target directory for foreign data search.

    When searching foreign table FT1, foreign data in all foreign data files under the s3://data/ directory is the target of the search, so foreign data in foreign data files [1] to [14] shown in "Foreign data file configuration" are the target of the search.

Example 2:

Define a foreign table (FT2).

CREATE FOREIGN TABLE "FT2" ("C1" INTEGER,"C2" INTEGER)   ...1
  SERVER "SERVER1"                                       ...2
  OPTIONS (                                              ...3
            FORMAT CSV                                   ...4
           ,LOCATION 's3://data/2025/'                   ...5
           ,DELIMITER_CHAR '.'                           ...6
           ,ENCLOSING_CHAR '@'                           ...7
           ,HEADER_SPEC YES                              ...8
          )

Explanation:

  1. Specifies the table name of the foreign table and the column definitions of the foreign table.

  2. Specifies the name of the foreign server used by the foreign table.

  3. After OPTIONS, foreign table options are specified.

  4. Specifies the data format of the foreign data. Specifies CSV because the foreign data is in CSV format.

  5. Specifies the path for target directory for foreign data search.

    When searching foreign table FT2, foreign data in all foreign data files under the s3://data/2025/ directory is the target of the search, so foreign data in foreign data files [11] to [14] shown in "Foreign data file configuration" are the target of the search.

  6. Specifies the delimiting character (.) of the foreign data in CSV format.

  7. Specifies the enclosing character (@) of the foreign data in CSV format.

  8. Specifies that the first line of foreign data in CSV format is read as a header row.

■If a partition key column is specified

The following is an example of defining a foreign table that specifies a partition key column.

Suppose the structure of the foreign data file that contains the foreign data is as follows.

[Figure]

Examples

Define a foreign table (ACCESSLOG).

CREATE FOREIGN TABLE "ACCESSLOG" ("ID" INTEGER,"USERID" CHAR(5))      ...1
  PARTITIONED BY ("YEAR" INTEGER,"MONTH" INTEGER,"DAY" INTEGER)       ...2
  SERVER "SERVER1"                                                    ...3
  OPTIONS (                                                           ...4
            FORMAT CSV                                                ...5
           ,LOCATION 's3://data/'                                     ...6
           ,"YEAR".TYPE ENUM                                          ...7
           ,"YEAR".VALUE '2024,2025'                                  ...7
           ,"MONTH".TYPE INTEGER                                      ...8
           ,"MONTH".RANGE_MIN 1                                       ...8
           ,"MONTH".RANGE_MAX 2                                       ...8
           ,"MONTH".INTERVAL 1                                        ...8
           ,"MONTH".DIGITS 2                                          ...8
           ,"DAY".TYPE INTEGER                                        ...9
           ,"DAY".RANGE_MIN 1                                         ...9
           ,"DAY".RANGE_MAX 2                                         ...9
           ,"DAY".INTERVAL 1                                          ...9
           ,"DAY".DIGITS 2                                            ...9
           ,LOCATION.TEMPLATE 's3://data/${YEAR}/${MONTH}/${DAY}/'    ...10
         )

Explanation:

  1. Specifies the table name of the foreign table and the column definitions of the foreign table.

  2. Defines the partition key columns. In this example, YEAR, MONTH, and DAY are defined as partition key columns.

  3. Specifies the name of the foreign server used by the foreign table.

  4. After OPTIONS, foreign table options are specified.

  5. Specifies the data format of the foreign data. Specifies CSV because the foreign data is in CSV format.

  6. Specifies the path for target directory for foreign data search.

  7. Specifies properties of the partition key column YEAR. The partition key column YEAR is defined to have the values 2024 and 2025.

  8. Specifies the properties of the partition key column MONTH. The partition key column MONTH is defined to have the values 1 and 2 (the values used as variable values in the template directory path are 01 and 02).

  9. Specifies the properties of the partition key column DAY. The partition key column DAY is defined to have the values 1 and 2 (the values used as variable values in the template directory path are 01 and 02).

  10. Specifies the template directory path.

    When searching the foreign table ACCESSLOG, the directory determined based on the specification of the template directory path becomes the target directory for foreign data search, and the foreign data in the foreign data files under this directory becomes the search target. Foreign data in the foreign data files [1]-[4], [7], [8], [11], [12] shown in "Foreign data file configuration" will be searched.

An example of a SELECT statement with a partition key column is shown below.

Example 1:
SELECT "YEAR","MONTH","DAY","USERID" FROM "ACCESSLOG"
    ORDER BY "YEAR","MONTH","DAY","USERID"

The underlined part specifies the partition key columns.

In this case, the foreign data in the foreign data files [1]-[4], [7], [8], [11], and [12] shown in "Foreign data file configuration" will be searched according to the template directory path specification defined in the CREATE FOREIGN TABLE statement.

<Example of the execution result of a SELECT statement>

[Figure]

Example 2:
SELECT "YEAR","MONTH","DAY","USERID" FROM "ACCESSLOG"
  WHERE "DAY" = 2
    ORDER BY "YEAR","MONTH","DAY","USERID"

The underlined part specifies the partition key columns.

The search condition specified in the WHERE clause narrows the value of the partition key column DAY to 2 (the value used as variable value in the template directory path is 02), so the foreign data in the foreign data files [3], [4], and [8] shown in "Foreign data file configuration" becomes the target of the search.

<Example of the execution result of a SELECT statement>

[Figure]

Example 3:
SELECT "YEAR","MONTH","DAY","USERID" FROM "ACCESSLOG"
  WHERE "DAY" = 2 AND "USERID" IN ('U0005','U0010')
    ORDER BY "YEAR","MONTH","DAY","USERID"

The underlined part specifies the partition key columns.

The search condition specified in the WHERE clause ("DAY" = 2) sets the value of the partition key column DAY to 2 (the value used as variable value in the template directory path is 02), so the foreign data in the foreign data files [3], [4], and [8] shown in "Foreign data file configuration" will be searched. The results are then returned according to the other search condition, "USERID" IN ('U005','U0010').

<Example of the execution result of a SELECT statement>

[Figure]

■If defining a foreign table that reads foreign data managed by an Iceberg table
Example 1

Define a foreign table (REST_ACCESSLOG).

Access foreign data using an Iceberg table. The catalog uses the REST Catalog.

CREATE FOREIGN TABLE "REST_ACCESSLOG" ("ID" INT,"USERID" CHAR(5))
  SERVER "SERVER1"
  OPTIONS (
     FORMAT ICEBERG                            ...1
    ,CATALOG_TYPE REST                         ...2
    ,CATALOG_NAMESPACE 'analytics_sales'
    ,CATALOG_URI 'https://access-api/catalog/iceberg'
    ,WAREHOUSE_NAME 'accesslog_catalog'
    ,AUTH_TOKEN_URI 'https://auth-api/realms/rest/protocol/openid-connect/token'
    ,AUTH_CLIENT_ID 'rest-catalog-id'
    ,AUTH_CLIENT_SECRET 'secret1234567890'
    ,AUTH_ALLOWED_SCOPES 'catalog'
    )

Explanation:

  1. Specifies ICEBERG for the foreign data format specification.

  2. Specifies the type of catalog used by Iceberg. Specify REST when using the REST Catalog.

Example 2

Define a foreign table (GLUE_ACCESSLOG).

Access foreign data using an Iceberg table. The catalog uses the Glue Catalog.

CREATE FOREIGN TABLE "GLUE_ACCESSLOG" ("ID" INT,"USERID" CHAR(5))
    SERVER "SERVER1"
    OPTIONS (
             FORMAT ICEBERG                            ...1
            ,CATALOG_TYPE GLUE                         ...2
            ,CATALOG_NAMESPACE 'analytics_sales'
            ,CATALOG_REGION 'ap-northeast-1'
            ,CATALOG_ID '123456789012'
            )

Explanation:

  1. Specifies ICEBERG for the foreign data format specification.

  2. Specifies the type of catalog used by Iceberg. Specify GLUE when using the Glue Catalog.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.