python连接hive批量补充时间分区特征

数据分析师之家

已于 2022-12-01 23:01:17 修改

阅读量480

点赞数

CC 4.0 BY-SA版权

分类专栏： hive Python神器机器学习文章标签： hive 大数据 hadoop

于 2019-08-07 17:20:47 首次发布

本文链接：https://blog.csdn.net/fwj_ntu/article/details/98763255

本文介绍了如何使用Python连接Hive数据库，通过封装SQL代码并获取日期参数，实现批量执行SQL脚本来补充时间分区特征，最终成功插入数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. python连接数据库

from pyhive import hive

conn = hive.Connection(
        host= 'xxx xxx', 
        port= xxx, 
        auth='CUSTOM',
        username='your user name', 
        password='your password',
        database='default')
cursor = conn.cursor()

2. SQL代码封装

设计为可传参方式

def get_sql(d):
    d = "'"+ d + "'"
    add_sql_horizon = """
    CREATE table tmp.horizon_feature as
    with 
    tmp_dlr_cstm as(
        select dealer_id,
        sum(
            case when DateDiff("""+d+""",created_time)<=30 and date_from not in (0,3) then 1
            else 0
            end
        ) as pcust_acc_1m_dlr_off
        from dcs.nt_tp_p_customers
        group by dealer_id
    )

    select   
        nvl(cast(d1.dealer_id as string),'') as dealer_id,
        nvl(cast(d1.dealer_code as string),'') as dealer_code,
        nvl(cast(d1.company_id as string),'') as company_id,
        nvl(c