elasticsearch-hive外部表插入数据到elasticsearch

1.创建hive源表，并插入数据

 

1.1创建hive表

drop table if exists a_usr_overview_d;
CREATE TABLE
    IF NOT EXISTS a_usr_overview_d
    (
        day_id string COMMENT 'Daily account period(for example:yyyymmdd)',
        online_usr_cnt string COMMENT 'Number of users on the network',
        offline_usr_cnt string COMMENT 'Number of users off the network',
        new_usr_cnt string COMMENT 'New users'
    )
comment 'es测试表'
partitioned by (p_day_id string comment 'Daily zoning')
row format delimited fields terminated by '\t' null defined as 'null' stored as textfile;

1.2往hive表插入数据

insert into table a_usr_overview_d partition(p_day_id='20200202')
select 20200201,  320,  2,  15
union all
select 20200202,  340,  3,  20
union all
select 20200203,  360,  6,  20;

2.elasticsearch-hive外部表创建

2.1修改es的端口，索引

drop table if exists a_usr_overview_d_es;
create external table a_usr_overview_d_es (
 day_id                     string    comment 'Daily account period(for example:yyyymmdd)'
,online_usr_cnt             string    comment 'Number of users on the network'
,offline_usr_cnt            string    comment 'Number of users off the network'
,new_usr_cnt                string    comment 'New users'
)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES(
'es.resource' = 'a_usr_overview_d_20200202/a_usr_overview_d_20200202',
'es.nodes'='172.21.72.166',
'es.port'='9200',
'es.nodes.wan.only' = 'true',
'es.index.auto.create' = 'true',
'es.net.http.auth.pass'='',
'es.net.http.auth.user'='',
'es.index.refresh_interval' = '-1',
'es.index.number_of_replicas' = '0',
'es.batch.write.retry.count' = '6',
'es.batch.write.retry.wait' = '60s');

若报错如下，则说明hive中缺少elasticsearch-hadoop-6.8.2.jar对应的jar依赖，即可：
FAILED: SemanticException Cannot find class 'org.elasticsearch.hadoop.hive.EsStorageHandler'

hive> add jar /home/bdp/shangeshishi/bigdata/smartEs/elasticsearch-hadoop-6.8.2.jar;
Added [/home/bdp/shangeshishi/bigdata/smartEs/elasticsearch-hadoop-6.8.2.jar] to class path
Added resources: [/home/bdp/shangeshishi/bigdata/smartEs/elasticsearch-hadoop-6.8.2.jar]
hive> list jars;
/home/bdp/shangeshishi/bigdata/smartEs/elasticsearch-hadoop-6.8.2.jar

永久生产的方法hive-site.xml 添加配置
<property>
  <name>hive.aux.jars.path</name>
  <value>/path/elasticsearch-hadoop-xxx.jar</value>
  <description>A comma separated list (with no spaces) of the jar files</description>
</property>

2.2插入数据到es

hive>  insert overwrite table a_usr_overview_d_es select day_id,online_usr_cnt,offline_usr_cnt,new_usr_cnt from a_usr_overview_d;

2.3head查看对应索引数据