Example: Querying a Foreign Table with Parquet Data - Advanced SQL Engine - Teradata Database

Table Definition

This statement creates the foreign table maps_ctg_alltypes_01_table and specifies the authorization DefAuth. This table definition defines a column with the corresponding Teradata data type for each Parquet logical data type. The column definitions must match the data formats and positions in the Parquet file. Parquet foreign tables can only be partitioned by column.

Create the DefAuth authorization object, if not already done. See Example: Creating an Authorization Object.

CREATE MULTISET FOREIGN TABLE maps_ctg_alltypes_01_table,FALLBACK
,EXTERNAL SECURITY DEFINER TRUSTED DefAuth
(
          Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
          id_col BIGINT NOT NULL,
          simple_boolean_col BYTEINT,
          simple_int32_col INT,
          decimal_int32_col DECIMAL(9,3),
          date_int32_col DATE,
          time_millis_int32_col TIME,
          uint8_int32_col SMALLINT,
          uint16_int32_col INTEGER,
          int8_int32_col BYTEINT,
          int16_int32_col SMALLINT,
          uint32_int32_col BIGINT,
          simple_int64_col BIGINT,
          uint64_int64_col DECIMAL(20,0),
          decimal_int64_col DECIMAL(18,4),
          time_micros_int64_col TIME,
          timestamp_millis_int64_col TIMESTAMP,
          timestamp_micros_int64_col TIMESTAMP,
          timestamp_int96_col TIMESTAMP,
          simple_float_col FLOAT,
          simple_double_col DOUBLE PRECISION,
          utf8_bytearray_col VARCHAR(1024) CHARACTER SET UNICODE,
          decimal_bytearray_col DECIMAL(32,6),
          json_bytearray_col JSON(32000) INLINE LENGTH 32000 CHARACTER SET UNICODE,
          bson_bytearray_col JSON(64000) INLINE LENGTH 64000 STORAGE FORMAT BSON,
          enum_col VARCHAR(1024) CHARACTER SET UNICODE,
          decimal_fl_bytearray_col DECIMAL(38,8),
          interval_fl_bytearray_col VARCHAR(50) CHARACTER SET UNICODE,
          uuid_col BYTE(16)
)
USING(
  LOCATION  ('external_file_path')
  PATHPATTERN  ('$Var1/$Var2/$var3/$Var4')
  STOREDAS  ('PARQUET')
  MANIFEST  ('FALSE')
  )
NO PRIMARY INDEX
PARTITION BY COLUMN;

Sample Query and Results

This statement retrieves a row count, maximum, and minimum values from the column time_millis_int32_col.

SELECT COUNT(time_millis_int32_col) AS count_col
,MAX(time_millis_int32_col) AS max_col
,MIN(time_millis_int32_col) AS min_col
FROM maps_ctg_alltypes_01_table;

The results display as follows.

  count_col          max_col          min_col
-----------  ---------------  ---------------
        996  16:42:15.916000  16:34:24.938000