ORC

This topic uses orders.tbl as an example to describe how to convert text files to ORC files.

Procedure

1. Create an OSS schema.

   CREATE SCHEMA dla_oss_db with DBPROPERTIES(
     catalog='oss',
     location 'oss://dlaossfile1/TPC-H/'
     )

2. Create a table named orders_txt in DLA. Set LOCATION to the path of orders.tbl in OSS.

   CREATE EXTERNAL TABLE orders_txt (
         O_ORDERKEY INT, 
         O_CUSTKEY INT, 
         O_ORDERSTATUS STRING, 
         O_TOTALPRICE DOUBLE, 
         O_ORDERDATE DATE, 
         O_ORDERPRIORITY STRING, 
         O_CLERK STRING, 
         O_SHIPPRIORITY INT, 
         O_COMMENT STRING
     ) 
     ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
     STORED AS TEXTFILE LOCATION 'oss://dlaossfile1/TPC-H/orders/orders.tbl'

3. Create the target table orders_orc in DLA. Set LOCATION to the required path in OSS.

   

   Note: LOCATION must be an existing directory in OSS and ended with /.

   CREATE EXTERNAL TABLE orders_orc (
        O_ORDERKEY INT, 
        O_CUSTKEY INT, 
        O_ORDERSTATUS STRING, 
        O_TOTALPRICE DOUBLE, 
        O_ORDERDATE DATE, 
        O_ORDERPRIORITY STRING, 
        O_CLERK STRING, 
        O_SHIPPRIORITY INT, 
        O_COMMENT STRING
    ) 
    STORED AS ORC LOCATION 'oss://dlaossfile1/TPC-H/orders_orc/'

   STORED AS ORC: indicates that the table is stored in ORC format.

4. Run the INSERT...SELECT statement to insert data from the orders_txt table to the orders_orc table.

   INSERT INTO orders_orc SELECT * FROM orders_txt;

5. View the data in the orders_orc table.

   After the INSERT...SELECT statement is executed, view the ORC file created in OSS.

   

More information

Create a table in ORC format