Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· openaiHugging Face Suzhen/CodeChat

理解下面用hivesql判断表是否为空语句:SELECT ‘hllcompare.hlldm_dmapppointmotiontrendv11hin’ AS tablename…

  1. User

    理解下面用hivesql判断表是否为空语句:SELECT ‘hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in’ AS table_name , CASE WHEN COUNT(1) > 0 THEN ‘Not empty’ ELSE ‘empty’ END AS status FROM ( SELECT 1 FROM hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in WHERE dt = ‘2024-04-25’ LIMIT 1 ) t。现在给你更多的表名,请你按照上面给你hivesql一样的判断方法来写hivesql。只需要更改表名,其他的不要改变。这些表是:hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in hll_compare.hll_dwb__dwb_lbs_compass_expo_order_driver_area_1d_in hll_compare.hll_dwb__dwb_order_expose_chain_1d_in hll_compare.hll_dm__dm_compass_user_funnel_behavior_1d_in hll_compare.hll_dwb__dwb_sensor_driver_order_expo_click_slide_detail_1d_in

  2. Assistant

    下面是根据给定表名修改后的hivesql语句:

    SELECT 'hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in' AS table_name
    , CASE
    WHEN COUNT(1) > 0 THEN 'Not empty'
    ELSE 'empty'
    END AS status
    FROM (
    SELECT 1
    FROM hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    ) t;
    
    SELECT 'hll_compare.hll_dwb__dwb_lbs_compass_expo_order_driver_area_1d_in' AS table_name
    , CASE
    WHEN COUNT(1) > 0 THEN 'Not empty'
    ELSE 'empty'
    END AS status
    FROM (
    SELECT 1
    FROM hll_compare.hll_dwb__dwb_lbs_compass_expo_order_driver_area_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    ) t;
    
    SELECT 'hll_compare.hll_dwb__dwb_order_expose_chain_1d_in' AS table_name
    , CASE
    WHEN COUNT(1) > 0 THEN 'Not empty'
    ELSE 'empty'
    END AS status
    FROM (
    SELECT 1
    FROM hll_compare.hll_dwb__dwb_order_expose_chain_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    ) t;
    
    SELECT 'hll_compare.hll_dm__dm_compass_user_funnel_behavior_1d_in' AS table_name
    , CASE
    WHEN COUNT(1) > 0 THEN 'Not empty'
    ELSE 'empty'
    END AS status
    FROM (
    SELECT 1
    FROM hll_compare.hll_dm__dm_compass_user_funnel_behavior_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    ) t;
    
    SELECT 'hll_compare.hll_dwb__dwb_sensor_driver_order_expo_click_slide_detail_1d_in' AS table_name
    , CASE
    WHEN COUNT(1) > 0 THEN 'Not empty'
    ELSE 'empty'
    END AS status
    FROM (
    SELECT 1
    FROM hll_compare.hll_dwb__dwb_sensor_driver_order_expo_click_slide_detail_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    ) t;
    

    这些查询将分别判断给定的表是否为空,如果表中具有符合条件的行,则返回"Not empty",否则返回"empty"。

  3. User

    上面hivesql可以合并成一条来执行,请你修改

  4. Assistant

    下面是修改后的hivesql语句,将多条查询合并为一条:

    SELECT 'hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in' AS table_name
    , CASE
    WHEN COUNT(1) > 0 THEN 'Not empty'
    ELSE 'empty'
    END AS status
    FROM (
    SELECT 1 AS cnt
    FROM hll_compare.hll_dm__dm_app_point_motion_trend_v1_1h_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    
    UNION ALL
    
    SELECT 1
    FROM hll_compare.hll_dwb__dwb_lbs_compass_expo_order_driver_area_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    
    UNION ALL
    
    SELECT 1
    FROM hll_compare.hll_dwb__dwb_order_expose_chain_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    
    UNION ALL
    
    SELECT 1
    FROM hll_compare.hll_dm__dm_compass_user_funnel_behavior_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    
    UNION ALL
    
    SELECT 1
    FROM hll_compare.hll_dwb__dwb_sensor_driver_order_expo_click_slide_detail_1d_in
    WHERE dt = '2024-04-25'
    LIMIT 1
    ) t;
    

    这条查询会一次性判断多个表是否为空,并返回对应的状态。

  5. User

    你好

  6. Assistant

    你好!有什么可以帮助你的吗?如果有任何问题,随时告诉我哦。

  7. User

    请详细解释以下脚本: dt=1tablesfilename=1 tables_file_name=2 echo "arges: dt{dt} {tables_file_name}" hadoop fs -get /user/issac.zhang/${tables_file_name}

    参数定义

    while IFS= read -r table_name do lefe_table=tablenamerighttable=hllcompare.table_name right_table=hll_compare.{lefe_table//./__} sql="insert overwrite table hll_compare.compare_detail partition(table_name='lefetable′,dt=′{lefe_table}', dt='{dt}') select CASE WHEN a.key IS NULL THEN b.key ELSE a.key END, a.cols, b.cols from ( (select md5(concat(',', *)) as key, concat(',', *) as cols from lefetablewheredt=′{lefe_table} where dt ='{dt}' ) a FULL JOIN (select md5(concat(',', *)) as key, concat(',', *) as cols from righttablewheredt=′{right_table} where dt ='{dt}' ) b ON a.key = b.key );"

    echo ${sql}
    hive -e "set tez.queue.name=bigquery; ${sql}"
    
    
    sql="insert into table hll_compare.compare_detail_result partition(dt='${dt}')
    select 
        '${lefe_table}',
        SUM(CASE WHEN source_cols IS NOT NULL and target_cols IS NOT NULL THEN 1 ELSE 0 END) AS matched,
        SUM(CASE WHEN source_cols IS NULL THEN 1 ELSE 0 END) AS source_non_match,
        SUM(CASE WHEN target_cols IS NULL THEN 1 ELSE 0 END) AS target_non_match,
        current_timestamp()
    from 
    hll_compare.compare_detail
    where table_name='${lefe_table}' and dt='${dt}'
    "
    echo ${sql}
    hive -e "set tez.queue.name=bigquery; ${sql}"
    

    done < "./$tables_file_name"

  8. Assistant

    这段脚本是用来比较两个表中数据的差异并生成比较结果的过程。下面逐行解释脚本的功能:

    1. dt=$1

      • 读取脚本执行时传入的第一个参数作为dt(日期)变量。
    2. tables_file_name=$2

      • 读取脚本执行时传入的第二个参数作为tables_file_name(表名文件名)变量。
    3. echo "arges: ${dt} ${tables_file_name}"

      • 输出传入的dt和tables_file_name参数。
    4. hadoop fs -get /user/issac.zhang/${tables_file_name}

      • 通过Hadoop命令从HDFS上下载指定文件,文件路径是/user/issac.zhang/${tables_file_name}。
    5. while IFS= read -r table_name

      • 开始一个循环,读取tables_file_name文件中的每一行作为table_name。
    6. lefe_table=$table_name

      • 将读取的表名赋值给lefe_table变量。
    7. right_table=hll_compare.${lefe_table//./__}

      • 修改表名的格式,将"."替换为"__",并加上前缀"hll_compare",赋值给right_table变量。
    8. sql=“…”

      • 构建一个Hive SQL查询语句,该查询语句用于比较两个表的数据并将结果插入hll_compare.compare_detail表。
    9. echo ${sql}

      • 输出生成的SQL语句。
    10. hive -e “set tez.queue.name=bigquery; ${sql}”

      • 执行上一步生成的SQL语句,设置Tez队列为"bigquery"。
    11. sql=“…”

      • 构建另一个Hive SQL查询语句,用于将比较结果插入hll_compare.compare_detail_result表。
    12. echo ${sql}

      • 输出生成的SQL语句。
    13. hive -e “set tez.queue.name=bigquery; ${sql}”

      • 执行上一步生成的SQL语句,设置Tez队列为"bigquery"。
    14. done < "./$tables_file_name"

      • 结束while循环,从tables_file_name文件中读取的表名。

    总体来说,这段脚本通过循环遍历给定的表名文件,对每个表名执行比较数据的操作,并将比较结果插入到指定的表中。