All Products
Search
Document Center

ORC

Last Updated: Jul 25, 2019

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

1

Procedure

  1. Create an OSS schema.

    1. CREATE SCHEMA dla_oss_db with DBPROPERTIES(
    2. catalog='oss',
    3. location 'oss://dlaossfile1/TPC-H/'
    4. )
  2. Create a table named orders_txt in DLA. Set LOCATION to the path of orders.tbl in OSS.

    1. CREATE EXTERNAL TABLE orders_txt (
    2. O_ORDERKEY INT,
    3. O_CUSTKEY INT,
    4. O_ORDERSTATUS STRING,
    5. O_TOTALPRICE DOUBLE,
    6. O_ORDERDATE DATE,
    7. O_ORDERPRIORITY STRING,
    8. O_CLERK STRING,
    9. O_SHIPPRIORITY INT,
    10. O_COMMENT STRING
    11. )
    12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
    13. 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.

    2

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

    1. CREATE EXTERNAL TABLE orders_orc (
    2. O_ORDERKEY INT,
    3. O_CUSTKEY INT,
    4. O_ORDERSTATUS STRING,
    5. O_TOTALPRICE DOUBLE,
    6. O_ORDERDATE DATE,
    7. O_ORDERPRIORITY STRING,
    8. O_CLERK STRING,
    9. O_SHIPPRIORITY INT,
    10. O_COMMENT STRING
    11. )
    12. 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.

    1. 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.

    3

More information

Create a table in ORC format