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