Complex Type

DataArts Fabric SQL supports reading complex type columns from ORC/PARQUET files. Currently, it only supports reading and converting these columns into the JSONB type.

Complex Type DDL

Currently, DDL only supports STRUCT and ARRAY types.

The definition syntax is as follows:

ARRAY: Defined with the syntax ARRAY<data_type>.
STRUCT: Defined with the syntax STRUCT<col_name : data_type, ...>.

Here, data_type indicates the data type, which can be either a basic type or a complex type. For details about basic types, refer to Mapping Between DataArts Fabric SQL and LakeFormation Data Types.

DDL example:

     
          -- Create an external table containing complex types.
CREATE EXTERNAL TABLE test_table (
    name TEXT,
    address STRUCT<city:TEXT, zip:INT>,
    tags ARRAY<TEXT>,
    logs ARRAY<STRUCT<term:TEXT, time:TIMESTAMP>>
) store as orc location 'obs://test/test1';

-- After creating the table, the corresponding column will show as jsonb (the converted type, with LakeFormation storing the full definition).
DESCRIBE test_table;

Conversion of Complex Types to JSONB

Current complex types including STRUCT and ARRAY are read through JSON. You need to explicitly convert the data to the appropriate data type after reading.

DQL examples (for detailed operator and function descriptions of jsonb, refer to JSON/JSONB Functions and Operators.

    
     
       
       
         -- -> Extracts elements of a JSON object or array by key or index.
SELECT name, address, tags, logs->0->'term' FROM test_table;
  name   |                address                |          tags           |  ?column?  
---------+---------------------------------------+-------------------------+------------
 Alice   | {"zip": 10001, "city": "New York"}    | ["friend", "colleague"] | "login"
 Bob     | {"zip": 90001, "city": "Los Angeles"} | ["family"]              | "purchase"
 Charlie | {"zip": 60601, "city": "Chicago"}     | ["friend", "hobby"]     | 
(3 rows)

-- ->> Extracts element and converts it to text.
SELECT name, address FROM test_table WHERE address->>'zip' = '10001';
 name  |              address               
-------+------------------------------------
 Alice | {"zip": 10001, "city": "New York"}
(1 row)

-- @> Array contains elements, where the left array includes all elements of the right array.
SELECT name, tags FROM test_table WHERE tags @> '["friend"]'::jsonb;
  name   |          tags           
---------+-------------------------
 Alice   | ["friend", "colleague"]
 Charlie | ["friend", "hobby"]
(2 rows)

-- Display type conversion. Use ->> to get as character and then perform type conversion.
SELECT (logs->0->>'time')::TIMESTAMP FROM test_table;
      timestamp      
---------------------
 2024-01-01 00:00:00
 2024-02-15 00:00:00

(3 rows)

        

      

    
   