1.在hive的安装包/apache-hive-2.3.4-bin/conf路径下的hive-site.xml文件里配置：
<property>
<name>hive.support.sql11.reserved.keywords</name>
<value>false</value>
</property>
此参数是关闭创建表字段有关键字段。也可以在sql语句之前设置
set hive.support.sql11.reserved.keywords=false;
sql语句中的关键字要用``，和mysql一样。
例如：
CREATE TABLE IF NOT EXISTS traffic.monitor_flow_action(
date string ,
monitor_id string ,
camera_id string ,
car string ,
action_time string ,
speed string ,
road_id string,
area_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
2.从hdfs中加载数据到hive表中，加载完后hdfs里的就自动删除(其实是mv了，移动到/user/hive/warehouse下了)。
例如：
CREATE TABLE IF NOT EXISTS traffic.monitor_flow_action(
date string ,
monitor_id string ,
camera_id string ,
car string ,
action_time string ,
speed string ,
road_id string,
area_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data inpath 'hdfs://mycluster/myfile/monitor_flow_action' into table traffic.monitor_flow_action;
CREATE TABLE IF NOT EXISTS traffic.monitor_camera_info(
monitor_id string ,
camera_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data inpath 'hdfs://mycluster/myfile/monitor_camera_info' into table traffic.monitor_camera_info;
从本地路径下加载文件数据到hive表中:
CREATE TABLE IF NOT EXISTS traffic.monitor_flow_action(
date string ,
monitor_id string ,
camera_id string ,
car string ,
action_time string ,
speed string ,
road_id string,
area_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data local inpath '/usr/java/monitor_flow_action' into table traffic.monitor_flow_action;
CREATE TABLE IF NOT EXISTS traffic.monitor_camera_info(
monitor_id string ,
camera_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data local inpath '/usr/java/monitor_camera_info' into table traffic.monitor_camera_info;
注意：从hdfs加数据和本地加载数据 load data 语法两个一个没有local，一个有local。 可以把sql文件放到一个文件里比如testCreateHiveSql.sql，然后用命令: hive -f testCreateHiveSql.sql 执行即可创建表和加载数据
完整示列: set hive.support.sql11.reserved.keywords=false;
CREATE TABLE IF NOT EXISTS traffic.monitor_flow_action(
date string ,
monitor_id string ,
camera_id string ,
car string ,
action_time string ,
speed string ,
road_id string,
area_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data local inpath '/root/test/monitor_flow_action' into table traffic.monitor_flow_action;
CREATE TABLE IF NOT EXISTS traffic.monitor_camera_info(
monitor_id string ,
camera_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data local inpath '/root/test/monitor_camera_info' into table traffic.monitor_camera_info;
