python-presto-timestamps和decimal(38,18)作为字符串返回？

ddarikpa 于 2021-06-24 发布在 Hive

关注(0)|答案(1)|浏览(331)

为什么presto timestamp/decimal（38,18）数据类型返回的是字符串（用u“”括起来），而不是python datetime/numeric类型？

普雷斯托jdbc：

从hive.x.y中选择typeof（col1）、typeof（col2）、typeof（col3）、typeof（col4）、typeof（col5）、typeof（col6）

结果是

timestamp timestamp bigint decimal（38,18）varchar varchar

desc hive.x.y

# result is

for_dt  timestamp   NO  NO  NO  NO  1
for_d   timestamp   NO  NO  NO  NO  2
for_h   bigint  NO  NO  NO  NO  3
value   decimal(38,18)  NO  NO  NO  NO  4
metric  varchar(2147483647) NO  NO  NO  NO  5
lat_lon varchar(2147483647) NO  NO  NO  NO  6

attempt 1

# python

from sqlalchemy.engine import create_engine
engine = create_engine('presto://u:p@host:port',connect_args={'protocol': 'https', 'requests_kwargs': {'verify': 'mypem'}})
result = engine.execute('select * from hive.x.y limit 1')
print(result.fetchall())

# result is

[(u'2010-02-18 03:00:00.000', u'2010-02-18 00:00:00.000', 3, u'-0.191912651062011660', u'hey', u'there')]

attempt 2

# python

from pyhive import presto
import requests
from requests.auth import HTTPBasicAuth

req_kw = {
'verify': 'mypem',
'auth': HTTPBasicAuth('u', 'p')
}

cursor = presto.connect(
host='host',
port=port,
protocol='https',
username='u',
requests_kwargs=req_kw,
).cursor()

query = '''select * from x.y limit 1'''
cursor.execute(query)
print cursor.fetchall()

# result is

[(u'2010-02-18 03:00:00.000', u'2010-02-18 00:00:00.000', 3, u'-0.191912651062011660', u'hey', u'there')]

Hive presto python sqlalchemy pyhive

来源：https://stackoverflow.com/questions/56842401/python-presto-timestamps-and-decimal38-18-returned-as-strings

1条答案

按热度按时间

vsikbqxv1#

从sql查询得到的输出来自该格式的数据库。
你有两个选择
自己Map数据（编写自己的orm）
学习使用orm
方案1
注意，我刚刚硬编码了您的查询结果在这里进行测试。

from sqlalchemy.engine import create_engine
from datetime import datetime
from decimal import Decimal

# 2010-02-18 03:00:00.000

dateTimeFormat = "%Y-%m-%d %H:%M:%S.%f"

class hivexy:
    def __init__(self, for_dt, for_d, for_h, value, metric, lat_lon):
        self.for_dt = for_dt
        self.for_d = for_d
        self.for_h = for_h
        self.value = value
        self.metric = metric
        self.lat_lon = lat_lon

    # Pretty Printing on print(hivexy)
    def __str__(self):
        baseString =  ("for_dt: {}\n"
                       "for_d: {}\n"
                       "for_h: {}\n"
                       "value: {}\n"
                       "metric: {}\n"
                       "lat_lon: {}\n")
        return baseString.format(for_dt, for_d, for_h, value, metric, lat_lon)

# engine = create_engine('presto://u:p@host:port',connect_args={'protocol': 'https', 'requests_kwargs': {'verify': 'mypem'}})

# results = engine.execute("select * from 'hive.x.y' limit 1")

results = [(u'2010-02-18 03:00:00.000', u'2010-02-18 00:00:00.000', 3, u'-0.191912651062011660', u'hey', u'there')]

hiveObjects = []

for row in results:
    for_dt = datetime.strptime(row[0], dateTimeFormat)
    for_d = datetime.strptime(row[1], dateTimeFormat)
    for_h = row[2]
    value = Decimal(row[3])
    metric = row[4]
    lat_lon = row[5]

    hiveObjects.append(hivexy(for_dt, for_d, for_h, value, metric, lat_lon))

for hiveObject in hiveObjects:
    print(hiveObject)

选项2使用反射-它查询数据库元数据中的字段类型，这样就不必在选项1中做所有的事情。

from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *

engine = create_engine('presto://u:p@host:port',connect_args={'protocol': 'https', 'requests_kwargs': {'verify': 'mypem'}})

# Reflection - SQLAlchemy will get metadata from database including field types

hiveXYTable = Table('hive.x.y', MetaData(bind=engine), autoload=True)
s = select([hiveXYTable]).limit(1)
results = engine.execute(s)

for row in results:
    print(row)

赞(0）回复(0）举报 2021-06-24

我来回答

python-presto-timestamps和decimal(38,18)作为字符串返回？

普雷斯托jdbc：

结果是

1条答案

相关问题

热门标签

最新问答