理解下面用hivesql判断表是否为空语句:SELECT ‘hllcompare.hlldm_dmapppointmotiontrendv11hin’ AS tablename…
- 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
- 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"。
- User
上面hivesql可以合并成一条来执行,请你修改
- 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;这条查询会一次性判断多个表是否为空,并返回对应的状态。
- User
你好
- Assistant
你好!有什么可以帮助你的吗?如果有任何问题,随时告诉我哦。
- User
请详细解释以下脚本: dt=2 echo "arges: {tables_file_name}" hadoop fs -get /user/issac.zhang/${tables_file_name}
参数定义
while IFS= read -r table_name do lefe_table={lefe_table//./__} sql="insert overwrite table hll_compare.compare_detail partition(table_name='{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 {dt}' ) a FULL JOIN (select md5(concat(',', *)) as key, concat(',', *) as cols from {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"
- Assistant
这段脚本是用来比较两个表中数据的差异并生成比较结果的过程。下面逐行解释脚本的功能:
-
dt=$1- 读取脚本执行时传入的第一个参数作为
dt(日期)变量。
- 读取脚本执行时传入的第一个参数作为
-
tables_file_name=$2- 读取脚本执行时传入的第二个参数作为
tables_file_name(表名文件名)变量。
- 读取脚本执行时传入的第二个参数作为
-
echo "arges: ${dt} ${tables_file_name}"- 输出传入的
dt和tables_file_name参数。
- 输出传入的
-
hadoop fs -get /user/issac.zhang/${tables_file_name}- 通过Hadoop命令从HDFS上下载指定文件,文件路径是
/user/issac.zhang/${tables_file_name}。
- 通过Hadoop命令从HDFS上下载指定文件,文件路径是
-
while IFS= read -r table_name- 开始一个循环,读取
tables_file_name文件中的每一行作为table_name。
- 开始一个循环,读取
-
lefe_table=$table_name- 将读取的表名赋值给
lefe_table变量。
- 将读取的表名赋值给
-
right_table=hll_compare.${lefe_table//./__}- 修改表名的格式,将"."替换为"__",并加上前缀"hll_compare",赋值给
right_table变量。
- 修改表名的格式,将"."替换为"__",并加上前缀"hll_compare",赋值给
-
sql=“…”- 构建一个Hive SQL查询语句,该查询语句用于比较两个表的数据并将结果插入
hll_compare.compare_detail表。
- 构建一个Hive SQL查询语句,该查询语句用于比较两个表的数据并将结果插入
-
echo ${sql}- 输出生成的SQL语句。
-
hive -e “set tez.queue.name=bigquery; ${sql}”- 执行上一步生成的SQL语句,设置Tez队列为"bigquery"。
-
sql=“…”- 构建另一个Hive SQL查询语句,用于将比较结果插入
hll_compare.compare_detail_result表。
- 构建另一个Hive SQL查询语句,用于将比较结果插入
-
echo ${sql}- 输出生成的SQL语句。
-
hive -e “set tez.queue.name=bigquery; ${sql}”- 执行上一步生成的SQL语句,设置Tez队列为"bigquery"。
-
done < "./$tables_file_name"- 结束
while循环,从tables_file_name文件中读取的表名。
- 结束
总体来说,这段脚本通过循环遍历给定的表名文件,对每个表名执行比较数据的操作,并将比较结果插入到指定的表中。
-