Hive的ADS层数据导出到MySQL/PostgreSQL

为了供前端去展示app层的数据&＃xff0c;提高数据的查询效率&＃xff0c;需要将hive中的数据导出到Mysql等关系型数据库中。

创建MySQL数据库和表

创建gmall_report数据库

注:SQL语句

CREATE DATABASE &＃96;gmall_report&＃96; CHARACTER SET &＃39;utf8&＃39; COLLATE &＃39;utf8_general_ci&＃39;;

 创建表

&＃xff08;1&＃xff09;访客统计

DROP TABLE IF EXISTS ads_visit_stats;
CREATE TABLE &＃96;ads_visit_stats&＃96; (&＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;is_new&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;新老标识,1:新,0:老&＃39;,&＃96;recent_days&＃96; INT NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;channel&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;渠道&＃39;,&＃96;uv_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;日活(访问人数)&＃39;,&＃96;duration_sec&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;页面停留总时长&＃39;,&＃96;avg_duration_sec&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;一次会话&＃xff0c;页面停留平均时长&＃39;,&＃96;page_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;页面总浏览数&＃39;,&＃96;avg_page_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;一次会话&＃xff0c;页面平均浏览数&＃39;,&＃96;sv_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;会话次数&＃39;,&＃96;bounce_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;跳出数&＃39;,&＃96;bounce_rate&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;跳出率&＃39;,PRIMARY KEY (&＃96;dt&＃96;,&＃96;recent_days&＃96;,&＃96;is_new&＃96;,&＃96;channel&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8;

 &＃xff08;2&＃xff09;页面路径分析

DROP TABLE IF EXISTS ads_page_path;
CREATE TABLE &＃96;ads_page_path&＃96; ( &＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;source&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;跳转起始页面&＃39;,&＃96;target&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;跳转终到页面&＃39;,&＃96;path_count&＃96; BIGINT(255) DEFAULT NULL COMMENT &＃39;跳转次数&＃39;,UNIQUE KEY (&＃96;dt&＃96;,&＃96;recent_days&＃96;,&＃96;source&＃96;,&＃96;target&＃96;) USING BTREE
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;3&＃xff09;用户统计

DROP TABLE IF EXISTS ads_user_total;
CREATE TABLE &＃96;ads_user_total&＃96; ( &＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,0:累积值,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;new_user_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;新注册用户数&＃39;,&＃96;new_order_user_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;新增下单用户数&＃39;,&＃96;order_final_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;下单总金额&＃39;,&＃96;order_user_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;下单用户数&＃39;,&＃96;no_order_user_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;未下单用户数(具体指活跃用户中未下单用户)&＃39;,PRIMARY KEY (&＃96;dt&＃96;,&＃96;recent_days&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8;

&＃xff08;4&＃xff09;用户变动统计

DROP TABLE IF EXISTS ads_user_change;
CREATE TABLE &＃96;ads_user_change&＃96; (&＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;user_churn_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;流失用户数&＃39;,&＃96;user_back_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;回流用户数&＃39;,PRIMARY KEY (&＃96;dt&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8;

&＃xff08;5&＃xff09;用户行为漏斗分析

DROP TABLE IF EXISTS ads_user_action;
CREATE TABLE &＃96;ads_user_action&＃96; (&＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;home_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;浏览首页人数&＃39;,&＃96;good_detail_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;浏览商品详情页人数&＃39;,&＃96;cart_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;加入购物车人数&＃39;,&＃96;order_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;下单人数&＃39;,&＃96;payment_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;支付人数&＃39;,PRIMARY KEY (&＃96;dt&＃96;,&＃96;recent_days&＃96;) USING BTREE
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;6&＃xff09;用户留存率分析

DROP TABLE IF EXISTS ads_user_retention;
CREATE TABLE &＃96;ads_user_retention&＃96; ( &＃96;dt&＃96; DATE DEFAULT NULL COMMENT &＃39;统计日期&＃39;,&＃96;create_date&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;用户新增日期&＃39;,&＃96;retention_day&＃96; BIGINT(20) NOT NULL COMMENT &＃39;截至当前日期留存天数&＃39;,&＃96;retention_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;留存用户数量&＃39;,&＃96;new_user_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;新增用户数量&＃39;,&＃96;retention_rate&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;留存率&＃39;,PRIMARY KEY (&＃96;create_date&＃96;,&＃96;retention_day&＃96;) USING BTREE
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;7&＃xff09;订单统计

DROP TABLE IF EXISTS ads_order_total;CREATE TABLE &＃96;ads_order_total&＃96; ( &＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;, &＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;order_count&＃96; BIGINT(255) DEFAULT NULL COMMENT &＃39;订单数&＃39;, &＃96;order_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;订单金额&＃39;, &＃96;order_user_count&＃96; BIGINT(255) DEFAULT NULL COMMENT &＃39;下单人数&＃39;,PRIMARY KEY (&＃96;dt&＃96;,&＃96;recent_days&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;8&＃xff09;各省份订单统计

DROP TABLE IF EXISTS ads_order_by_province;
CREATE TABLE &＃96;ads_order_by_province&＃96; (&＃96;dt&＃96; DATE NOT NULL,&＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;province_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;province_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;省份名称&＃39;,&＃96;area_code&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;地区编码&＃39;,&＃96;iso_code&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;国际标准地区编码&＃39;,&＃96;iso_code_3166_2&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;国际标准地区编码&＃39;,&＃96;order_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;订单数&＃39;,&＃96;order_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;订单金额&＃39;,PRIMARY KEY (&＃96;dt&＃96;, &＃96;recent_days&＃96; ,&＃96;province_id&＃96;) USING BTREE
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;9&＃xff09;品牌复购率

DROP TABLE IF EXISTS ads_repeat_purchase;
CREATE TABLE &＃96;ads_repeat_purchase&＃96; ( &＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;tm_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;品牌ID&＃39;,&＃96;tm_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;品牌名称&＃39;,&＃96;order_repeat_rate&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;复购率&＃39;,PRIMARY KEY (&＃96;dt&＃96; ,&＃96;recent_days&＃96;,&＃96;tm_id&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;10&＃xff09;商品统计

DROP TABLE IF EXISTS ads_order_spu_stats;
CREATE TABLE &＃96;ads_order_spu_stats&＃96; (&＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;recent_days&＃96; BIGINT(20) NOT NULL COMMENT &＃39;最近天数,1:最近1天,7:最近7天,30:最近30天&＃39;,&＃96;spu_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;商品ID&＃39;,&＃96;spu_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;商品名称&＃39;,&＃96;tm_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;品牌ID&＃39;,&＃96;tm_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;品牌名称&＃39;,&＃96;category3_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;三级品类ID&＃39;,&＃96;category3_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;三级品类名称&＃39;,&＃96;category2_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;二级品类ID&＃39;,&＃96;category2_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;二级品类名称&＃39;,&＃96;category1_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;一级品类ID&＃39;,&＃96;category1_name&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;一级品类名称&＃39;,&＃96;order_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;订单数&＃39;,&＃96;order_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;订单金额&＃39;, PRIMARY KEY (&＃96;dt&＃96;,&＃96;recent_days&＃96;,&＃96;spu_id&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8;

&＃xff08;11&＃xff09;活动统计

DROP TABLE IF EXISTS ads_activity_stats;
CREATE TABLE &＃96;ads_activity_stats&＃96; (&＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;activity_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;活动ID&＃39;,&＃96;activity_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;活动名称&＃39;,&＃96;start_date&＃96; DATE DEFAULT NULL COMMENT &＃39;开始日期&＃39;,&＃96;order_count&＃96; BIGINT(11) DEFAULT NULL COMMENT &＃39;参与活动订单数&＃39;,&＃96;order_original_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;参与活动订单原始金额&＃39;,&＃96;order_final_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;参与活动订单最终金额&＃39;,&＃96;reduce_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;优惠金额&＃39;,&＃96;reduce_rate&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;补贴率&＃39;,PRIMARY KEY (&＃96;dt&＃96;,&＃96;activity_id&＃96; )
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

&＃xff08;12&＃xff09;优惠券统计

DROP TABLE IF EXISTS ads_coupon_stats;
CREATE TABLE &＃96;ads_coupon_stats&＃96; (&＃96;dt&＃96; DATE NOT NULL COMMENT &＃39;统计日期&＃39;,&＃96;coupon_id&＃96; VARCHAR(255) NOT NULL COMMENT &＃39;优惠券ID&＃39;,&＃96;coupon_name&＃96; VARCHAR(255) DEFAULT NULL COMMENT &＃39;优惠券名称&＃39;,&＃96;start_date&＃96; DATE DEFAULT NULL COMMENT &＃39;开始日期&＃39;, &＃96;rule_name&＃96; VARCHAR(200) DEFAULT NULL COMMENT &＃39;优惠规则&＃39;,&＃96;get_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;领取次数&＃39;,&＃96;order_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;使用(下单)次数&＃39;,&＃96;expire_count&＃96; BIGINT(20) DEFAULT NULL COMMENT &＃39;过期次数&＃39;,&＃96;order_original_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;使用优惠券订单原始金额&＃39;,&＃96;order_final_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;使用优惠券订单最终金额&＃39;,&＃96;reduce_amount&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;优惠金额&＃39;,&＃96;reduce_rate&＃96; DECIMAL(16,2) DEFAULT NULL COMMENT &＃39;补贴率&＃39;,PRIMARY KEY (&＃96;dt&＃96;,&＃96;coupon_id&＃96; )
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 ROW_FORMAT&＃61;DYNAMIC;

Sqoop导出脚本

1&＃xff09;编写Sqoop导出脚本

在/home/muzili/bin目录下创建脚本hdfs_to_mysql.sh

[muzili&＃64;hadoop102 bin]$ vim hdfs_to_mysql.sh

在脚本中填写如下内容&＃xff1a;

#!/bin/bashhive_db_name&＃61;gmall
mysql_db_name&＃61;gmall_reportexport_data() {
/opt/module/sqoop/bin/sqoop export \
--connect "jdbc:mysql://hadoop102:3306/${mysql_db_name}?useUnicode&＃61;true&characterEncoding&＃61;utf-8" \
--username root \
--password 000000 \
--table $1 \
--num-mappers 1 \
--export-dir /warehouse/$hive_db_name/ads/$1 \
--input-fields-terminated-by "\t" \
--update-mode allowinsert \
--update-key $2 \
--input-null-string &＃39;\\N&＃39; \
--input-null-non-string &＃39;\\N&＃39;
}case $1 in"ads_activity_stats" )export_data "ads_activity_stats" "dt,activity_id";;"ads_coupon_stats" )export_data "ads_coupon_stats" "dt,coupon_id";;"ads_order_by_province" )export_data "ads_order_by_province" "dt,recent_days,province_id";;"ads_order_spu_stats" )export_data "ads_order_spu_stats" "dt,recent_days,spu_id";;"ads_order_total" )export_data "ads_order_total" "dt,recent_days";;"ads_page_path" )export_data "ads_page_path" "dt,recent_days,source,target";;"ads_repeat_purchase" )export_data "ads_repeat_purchase" "dt,recent_days,tm_id";;"ads_user_action" )export_data "ads_user_action" "dt,recent_days";;"ads_user_change" )export_data "ads_user_change" "dt";;"ads_user_retention" )export_data "ads_user_retention" "create_date,retention_day";;"ads_user_total" )export_data "ads_user_total" "dt,recent_days";;"ads_visit_stats" )export_data "ads_visit_stats" "dt,recent_days,is_new,channel";;"all" )export_data "ads_activity_stats" "dt,activity_id"export_data "ads_coupon_stats" "dt,coupon_id"export_data "ads_order_by_province" "dt,recent_days,province_id"export_data "ads_order_spu_stats" "dt,recent_days,spu_id"export_data "ads_order_total" "dt,recent_days"export_data "ads_page_path" "dt,recent_days,source,target"export_data "ads_repeat_purchase" "dt,recent_days,tm_id"export_data "ads_user_action" "dt,recent_days"export_data "ads_user_change" "dt"export_data "ads_user_retention" "create_date,retention_day"export_data "ads_user_total" "dt,recent_days"export_data "ads_visit_stats" "dt,recent_days,is_new,channel";;
esac

 关于导出update还是insert的问题

• --update-mode&＃xff1a;

updateonly   只更新&＃xff0c;无法插入新数据

        allowinsert   允许新增 

• --update-key&＃xff1a;允许更新的情况下&＃xff0c;指定哪些字段匹配视为同一条数据&＃xff0c;进行更新而不增加。多个字段用逗号分隔。

• --input-null-string和--input-null-non-string&＃xff1a;

分别表示&＃xff0c;将字符串列和非字符串列的空串和“null”转义。

官网地址&＃xff1a;http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html

Sqoop will by default import NULL values as string null. Hive is however using string \N to denote NULL values and therefore predicates dealing with NULL(like IS NULL) will not work correctly. You should append parameters --null-string and --null-non-string in case of import job or --input-null-string and --input-null-non-string in case of an export job if you wish to properly preserve NULL values. Because sqoop is using those parameters in generated code, you need to properly escape value \N to \\N:

Hive中的Null在底层是以“\N”来存储&＃xff0c;而MySQL中的Null在底层就是Null&＃xff0c;为了保证数据两端的一致性。在导出数据时采用--input-null-string和--input-null-non-string两个参数。导入数据时采用--null-string和--null-non-string。

2&＃xff09;执行Sqoop导出脚本

[muzili&＃64;hadoop102 bin]$ chmod 777 hdfs_to_mysql.sh

[muzili&＃64;hadoop102 bin]$ hdfs_to_mysql.sh all