This topic uses orders.tbl as an example to describe how to convert text files to ORC files.
Procedure
Create an OSS schema.
CREATE SCHEMA dla_oss_db with DBPROPERTIES(
catalog='oss',
location 'oss://dlaossfile1/TPC-H/'
)
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'
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.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;
View the data in the orders_orc table.
After the
INSERT...SELECT
statement is executed, view the ORC file created in OSS.