在工作中我们经常会用到数据库中的数据,接下来我们就学习一下,如何快速的从python中读取hive库中的数据
1、安装包
首先我们安装一下python链接hive的包impyla,在impyla包安装的时候,就直接将其依赖的包thrift、pure-sasl、thrift_sasl都安装好了
pip install impyla
2、链接hive数据库并提取数据
1)提取全部数据/一条数据
conn = connect(host = '172.16.211.30', port = 10000, user = "dw", auth_mechanism = 'PLAIN', password = "123456", database = "dws_xb")
cursor = conn.cursor()
sql = "select * from dws_xb.s_goods_orders where dt='2022-01-03' limit 100" # 这里是数据提取的sql语句
cursor.execute(sql)
data = df(cursor.fetchall()) # fetchall:提取全部数据,fetchone:提取一条数据
cursor.close()
conn.close()
'''
host:主机地址
port:端口号
user:用户名称
password:密码
database:数据库名称
这些参数可以询问数仓人员得知哦~
'''
2)获取每个表的详细情况
conn = connect(host = '172.16.211.30', port = 10000, user = "dw", auth_mechanism = 'PLAIN', password = "123456", database = "dws_xb")
cursor = conn.cursor()
sql1 = 'show tables'
cursor.execute(sql1)
tables = df(cursor.fetchall())
data = df()
for i in tables[0]:
print(i)
sql = 'describe ' + i
cursor.execute(sql)
data = data.append(df(cursor.fetchall()))
cursor.close()
conn.close()