hive添加txt文件转oec表

1.数据格式

1 2 x
2 2 x
3 2 x
4 2 x

2.创建表

创建TEXTFILE格式的表

CREATE TABLE test_txt(id int,num int,txt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

指定分隔符为\t
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
指定换行符，不然可能会导入NULL数据
LINES TERMINATED BY '\n'

创建ORC格式的表

CREATE TABLE test_orc(id int,num int,txt string)
STORED AS ORC; 

3.上传数据到hdfs

hdfs dfs -put /opt/test.txt /test

4.加载数据

加载.txt文件到TEXTFILE格式的表

load data inpath '/test/test.txt' into table test_txt;

加载test_txt表数据到orc格式的表

INSERT INTO TABLE test_orc SELECT * FROM test_txt;