python读取es数据库数据并导入hive

需求

从es数据库导出数据到hive数仓

用到的库

import csv
import threading
import time
import logging
import traceback

from datetime import datetime, timedelta
import pandas as pd
from elasticsearch import Elasticsearch
from hdfs.client import InsecureClient
from impala.dbapi import connect
from impala.hiveserver2 import HiveServer2Cursor

大致流程如下：
读取es数据 ->落地生成文件->上传到hdfs->load导入
但遇到的坑不少，分别讲讲

读取es数据

首先要注意的点是es库导出时只能导出十条左右的数据，网上对此的资料不多，原因是es数据库默认每次只返回10条，需要使用游标来遍历才能全部获取，代码如下：

# 连接es数据库
es = Elasticsearch(host, timeout=3600,
                                # sniff_on_start = True,  # 连接前测试
                                # sniff_on_connection_fail=True,  # 节点没有响应时，进行刷新，重新连接
                                # sniffer_timeout=60,  # 每 60 秒刷新一次
                                # sniff_timeout=10,
                                retry_on_timeout=True,  # 超时时重试
                                max_retries=10  # 重试次数
                                )
# 这里定义scroll即为指定以游标方式，指定scroll为‘5m’的意思是该游标保存五分钟，size为每次返回2500条，body为申请条件
result_coucor = es.search(index="single_chat_record_v1", scroll='5m', size=2500, body=query)  
total = result_coucor['hits']['total']  # 总条数
logging.warning(f'single_chat_record_v1当天数据共{total}条')
# 初始化游标
scroll_id = result_coucor['_scroll_id']
# 此处循环总条数/每次返回数量的次数，以获取所有数据
for i in range(0, int(total / 2500) + 1):
     # 定义scroll_id=上面初始化的游标
     query_result = self.es.scroll(scroll_id=scroll_id, scroll='5m')
     # 重置游标，这里其实重置不重置都无所谓
     scroll_id = query_result['_scroll_id']
     records = []
     # 获取内容存入csv文件，本来用pandas进行json转换，后来觉得pandas会导致效率变慢，所以换成传统方式
     # 感兴趣的可以看我上一篇文章
     for va in query_result['hits']['hits']:
          va_list = [va.get('_source').get('id'), va.get('_source').get('content'),
                         va.get('_source').get('sendUserId'), va.get('_source').get('sendTime'),
                         va.get('_source').get('measureBack'), va.get('_source').get('measureBackTime'),
                         va.get('_source').get('status'), va.get('_source').get('contentType'),
                         va.get('_source').get('thirdMsgId'), va.get('_source').get('referenceMsgId'),
                         va.get('_source').get('chatWindowId')]
          records.append(va_list)
      with open('./file.csv','a') as fp:
           writer = csv.writer(fp, delimiter=chr(0x02))
           writer.writerows(records)

落地生成文件

上传到hdfs

这两步，可以并为一步，原因为在这里踩了坑，后来调整方案为不落地到本地，而是直接从es写入到hdfs
踩坑的点1：
一开始把文件落到本地，本打算使用hdfs dfs -put file.csv /file_path ，结果在执行过程中发现python本进程并不是直接在hive所在服务器执行的，而是通过hive相关模块远程连接另一台hive服务器来操作hive执行，所以程序落到本地后运行put，会报错hdfs不是内部命令。这里有点绕，其实意思就是有两台服务器，服务器A是执行python程序的，然后在python程序中远程连接服务器B，而B才是数仓真正地址，类似于我们在家远程控制公司的电脑。

后来决定直接写入hdfs，用到了hdfs模块，

pip instail hdfs
from hdfs.client import InsecureClient, Client

这里引出了另一个小坑2：
hdfs.client有两个类都可以实例化hdfs客户端，即连接hdfs（InsecureClient、Client）但网上基本只能搜到Client。连接代码如下：

client = Client("http://127.0.0.1:50070/")

该实例有可选参数
url：ip：端口
root：制定的hdfs根目录
proxy：制定登陆的用户身份
timeout：设置的超时时间
session:连接标识
可以发现，是没有指定用户的参数的（proxy是用户身份而不是用户，不要问什么是用户身份，因为我也不知道）,而hdfs的不同路径是需要不同的用户权限来进行读写的，后来在翻了源码后才发现还有一个类InsecureClient 也可以进行实例化：
它继承了Client，新增了用户参数，借此得以控制相关路径的读写权限。

client = InsecureClient(.hdfs_url, user='workrd', timeout=10000)

如果连接不上，报异常为
requests.exceptions.ConnectionError: HTTPConnectionPool(host=‘bj-hw-8f-18-162’, port=50075): Max retries exceeded with url: /webhdfs/v1/test.txt?op=OPEN&namenoderpcaddress=hMaster:9000&offset=0 (Caused by NewConnectionError(’<urllib3.connection.HTTPConnection object at 0x00000000035BAB38>: Failed to establish a new connection: [Errno 11004] getaddrinfo failed’,))
则需要在host文件中添加ip和名字的映射

ip 主机名

实例化之后用到的相关方法有：

status——获取路径的具体信息

client .status(hdfs_path, strict=True)

hdfs_path：就是hdfs路径
strict：设置为True时，如果hdfs_path路径不存在就会抛出异常，如果设置为False，如果路径为不存在，则返回None

delete—删除

>client.delete(hdfs_path, recursive=False)

recursive：删除文件和其子目录，设置为False如果不存在，则会抛出异常，默认为False

–write — 写入or创建

client.write(hdfs_path, data='', overwrite=False, append=True, encoding='utf-8')

data：要写入的数据
overwrite：为真时覆盖写
append：为真时追加写
这里遇到第三个坑3：当append=True，即追加写的时候，如果文件不存在则报异常文件不存在，所以需要提前先创建一个空文件

client.write(hdfs_path, data='')

如果不传入 data=’'则文件不会创建，所以传入data=空字符串当作空文件

到此，则hdfs文件生成成功，接下来就简单了，只需要执行hive命令load即可。

load导入

LOAD DATA INPATH '{file_path}/file.csv' OVERWRITE INTO TABLE database.table_name PARTITION (pt=pt);

完整代码如下：

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import csv
import threading
import time
import logging
import traceback

from datetime import datetime, timedelta
import pandas as pd
from elasticsearch import Elasticsearch
from hdfs.client import InsecureClient
from impala.dbapi import connect
from impala.hiveserver2 import HiveServer2Cursor

pd.set_option('display.max_rows', None, 'display.max_columns', None, 'max_colwidth', 200, 'expand_frame_repr', False)


class EsToOds:
    def __init__(self, host, hdfs_url, pt, yes_timestamp, timestamp, record_path=None, meta=None):
        self.record_path = record_path
        self.meta = meta
        self.pt = pt
        self.hdfs_url = hdfs_url
        self.yes_timestamp = yes_timestamp
        self.timestamp = timestamp
        # 连接hive
        self.con = connect(host=self.host, user=self.username, password=self.password, port=int(self.port),
                           auth_mechanism=self.auth)
        # 连接ES
        self.es = Elasticsearch(host, timeout=3600,
                                # sniff_on_start = True,  # 连接前测试
                                # sniff_on_connection_fail=True,  # 节点没有响应时，进行刷新，重新连接
                                # sniffer_timeout=60,  # 每 60 秒刷新一次
                                # sniff_timeout=10,
                                retry_on_timeout=True,  # 超时时重试
                                max_retries=10  # 重试次数
                                )
        # 连接hdfs
        self.client = InsecureClient(self.hdfs_url, user='workrd', timeout=10000)

    def single_chat_record_v1(self):
        “”“
        es数据写入到hdfs
        ”“”
        # 设置筛选条件，
        query = {
            "query": {
                "range": {
                    'sendTime': {
                        'gte': self.yes_timestamp,  # sendTime>=
                        'lte': self.timestamp   # sendTime<=
                    }
                }
            },
            "sort": [
                "_doc"  # 对数据不进行排序，加快查询速度
            ]
        }
        v__csv = 'path/file.csv'
        # 判断hdfs文件是否存在，如果存在则删除
        if self.client.status(v__csv, strict=False):
            self.client.delete(v__csv)
        # 创建空文件
        self.client.write(v__csv, data='')
        # 连接index索引，类似mysql的table，获取游标
        result_coucor = self.es.search(index="index", scroll='5m', size=2500, body=query)
        # 数据总数量
        total = result_coucor['hits']['total']
        logging.warning(f'single_chat_record_v1当天数据共{total}条')
        # 初始化游标
        scroll_id = result_coucor['_scroll_id']
        for i in range(0, int(total / 2500) + 1):
            # 通过游标获取数据
            query_result = self.es.scroll(scroll_id=scroll_id, scroll='5m')
            scroll_id = query_result['_scroll_id']
            records = []
            for va in query_result['hits']['hits']:
                va_list = [va.get('_source').get('id'), va.get('_source').get('content'),
                           va.get('_source').get('sendUserId'), va.get('_source').get('sendTime'),
                           va.get('_source').get('measureBack'), va.get('_source').get('measureBackTime'),
                           va.get('_source').get('status'), va.get('_source').get('contentType'),
                           va.get('_source').get('thirdMsgId'), va.get('_source').get('referenceMsgId'),
                           va.get('_source').get('chatWindowId')]
                records.append(va_list)
            # 追加写入到hdfs
            with self.client.write(v__csv, overwrite=False, append=True, encoding='utf-8') as fp:
                writer = csv.writer(fp, delimiter=chr(0x02))
                writer.writerows(records)
        logging.warning(f'生成hdfs文件{v__csv}成功')

    def execute_sql(self, sql='', hive_config=None):
        """执行sql方法"""
        cur: HiveServer2Cursor = self.con.cursor()
        try:
            cur.execute(sql, None, hive_config)
            execute_status = cur.status()
            print("execute_status: ", execute_status)
            logging.warning("Hive execute sql , return status: %s" % execute_status)
            return
        except Exception as e:
            logging.error('sql:' + sql)
            error = 'Hive execute failed! ERROR (%s): %s  error_msg: %s' % (
            e.args[0], e.args[0], traceback.format_exc())
            logging.error(error)
            raise Exception('Hive执行错误')
        # finally:
        #     cur.close()

    def to_hive(self):
        try:
            sql = f'''
                 LOAD DATA INPATH '{file_path}/file.csv' OVERWRITE INTO TABLE database.table_name PARTITION (pt='{self.pt}')
                     '''
            self.execute_sql(hsql)
        finally:
            self.con.close()

    def run(self):
        self.single_chat_record_v1()
        self.to_hive()


def main():
    time.perf_counter()
    yes_date = (datetime.now() + timedelta(days=-1)).strftime("%Y%m%d")
    timeArray = time.strptime(yes_date, "%Y%m%d")
    yes_timestamp = int(time.mktime(timeArray)) * 1000
    timestamp = yes_timestamp + 86400000
    to_friend = EsToOds(host, hdfs_url=hdfs_url, pt=yes_date, yes_timestamp=yes_timestamp, timestamp=timestamp)
    try:
        to_friend.run()
    finally:
        to_friend.es.close()
        print(time.perf_counter())



if __name__ == '__main__':
    main()

到这里就分享结束了，希望可以帮助到你！欢迎多交流！

补充

最后补充：
1.es.search中的body参数不再推荐，且在8.0版本完全弃用，取而代之的是query参数。使用上略有不同。
body的输入为：一个json字符串，其第一层key为固定字符‘query’，和可选字符串‘sort’等等, 在key=‘query’里有第二层key ，为筛选方式：如
‘range’-范围筛选；
‘match’–模糊查询；
‘term’–精准单指查询，类似于mysql中的where ziduan=‘a’
“terms”–精准多值查询，类似于mysql中的where ziduan in (a, b,c…)
参考文档：
es数据库查询条件
es数据库入门

query = {
            "query": {
                "range": {
                    'sendTime': {
                        'gte': self.yes_timestamp,  # sendTime>=
                        'lte': self.timestamp   # sendTime<=
                    }
                }
            },
            "sort": [
                "_doc"  # 对数据不进行排序，加快查询速度
            ]
        }
es.search(index="index", size=2500, body=query)

而替代品query同样为json字符串，不同的是弃用原第一层key‘query’，改成第一层key就是筛选方式，原来可选字符串‘sort’移至到search参数中：doc_type：

query = {
            "range": {
                'updateTime': {
                    'gte': yes_timestamp,
                    'lte': timestamp
                }
            }
    }
es.search(index="im_friend_v1", doc_type="_doc", query=query)

2.如果es数据库设置用户密码，则需要参数http_auth=(‘user’, ‘password’)

es = Elasticsearch(
        ['url'],
        http_auth=('user', 'password'),
        port=9200,
        use_ssl=False
    )

官方API如下：
es官方文档