数仓使用SQL脚本在数据库中添加初始数据示例

冷不防 2023-10-09 20:12 5阅读 0赞

#### 文章目录 ####

需要在虚拟机上开启数据库

![在这里插入图片描述][8b4d9e8a8caf4c62b7eeaa1195a8597a.png]  
![在这里插入图片描述][22cd629e9721427580bb7944e7f82eca.png]

点击确定后，可以点开这个连接，查看数据库信息

![在这里插入图片描述][8fac7c2bc7da4703bcccbf0b6386483a.png]

运行 init\_mysql.sql 创建mall 数据库

-- 设置sql_mode
    set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
    
    -- 创建数据库mall
    create database mall;
    
    -- 切换数据库
    use mall;
    
    -- 创建用户信息表
    CREATE TABLE t_user_info(
    	user_id        varchar(100) not null,
    	user_name      varchar(100) not null,
    	sex            varchar(10)  not null,
    	age            int not null,
    	country_code   varchar(100) not null,
    	province_code  varchar(100) not null,
    	city_code      varchar(100) not null
    )DEFAULT CHARSET='utf8';
    
    -- 创建订单表
    CREATE TABLE t_sale_order(
    	sale_id        varchar(100) not null,
    	user_id        varchar(100) not null,
    	goods_id       varchar(100) not null,
    	price          int not null,
    	sale_count     int not null,
    	total_price    int not null,
    	create_time    varchar(100) not null
    )DEFAULT CHARSET='utf8';
    
    -- 创建商品信息表
    CREATE TABLE dim_goods_info(
    	goods_id   varchar(100) not null,
    	goods_name varchar(100) not null
    )DEFAULT CHARSET='utf8';
    
    -- 创建国家信息表
    CREATE TABLE dim_country_info(
    	country_code   varchar(100) not null,
    	country_name   varchar(100) not null
    )DEFAULT CHARSET='utf8';
    
    -- 创建省份信息表
    CREATE TABLE dim_province_info(
    	province_code   varchar(100) not null,
    	province_name   varchar(100) not null,
    	country_code    varchar(100) not null
    )DEFAULT CHARSET='utf8';
    
    -- 创建城市信息表
    CREATE TABLE dim_city_info(
    	city_code     varchar(100) not null,
    	city_name     varchar(100) not null,
    	province_code varchar(100) not null
    )DEFAULT CHARSET='utf8';
    
    
    -- 用户信息表插入数据
    insert into t_user_info values('c001','王小名','男',22,'86','32','320100');
    insert into t_user_info values('c002','李虎','男',40,'86','32','320200');
    insert into t_user_info values('c003','韩静','女',26,'86','32','320600');
    insert into t_user_info values('c004','董冬','男',35,'86','32','321100');
    insert into t_user_info values('c005','张茗','男',21,'86','32','321200');
    insert into t_user_info values('c006','张一凡','男',56,'86','32','321300');
    insert into t_user_info values('c007','王花','女',20,'86','32','320100');
    insert into t_user_info values('c008','刘梦','女',31,'86','32','320600');
    insert into t_user_info values('u001','peter','男',30,'1','12','2233');
    insert into t_user_info values('u002','rose','女',22,'1','08','2345');
    insert into t_user_info values('u003','jack','男',26,'1','02','3663');
    insert into t_user_info values('u004','marel','男',31,'1','11','4567');
    commit;
    
    -- 订单表插入数据
    insert into t_sale_order values('s001','c002','g005',1099,1,1099,'2022-11-08 09:23:54');
    insert into t_sale_order values('s002','c002','g001',3000,2,6000,'2022-11-08 10:12:36');
    insert into t_sale_order values('s003','c004','g006',2899,1,2899,'2022-11-08 09:23:54');
    insert into t_sale_order values('s004','u001','g001',3000,1,3000,'2022-11-08 08:01:21');
    insert into t_sale_order values('s005','u002','g002',100,3,300,'2022-11-08 13:40:00');
    insert into t_sale_order values('s006','c006','g009',299,1,299,'2022-11-08 08:11:20');
    insert into t_sale_order values('s007','u003','g005',1099,1,1099,'2022-11-08 15:01:33');
    insert into t_sale_order values('s008','c006','g004',3000,1,3000,'2022-11-08 17:08:01');
    insert into t_sale_order values('s009','c005','g008',10,8,80,'2022-11-08 12:08:23');
    insert into t_sale_order values('s010','c006','g002',100,1,100,'2022-11-08 22:23:14');
    insert into t_sale_order values('s011','c006','g007',99,10,999,'2022-11-08 23:07:42');
    insert into t_sale_order values('s012','c007','g007',99,1,99,'2022-11-08 06:51:03');
    commit;
    
    -- 商品信息表插入数据
    insert into dim_goods_info values('g001','OPPO K9x 5G全网通手机');
    insert into dim_goods_info values('g002','儿童历史地理大百科全书 绘本礼盒典藏全40册');
    insert into dim_goods_info values('g003','欧珀莱 AUPRES 时光锁小紫钻抗皱紧实眼霜');
    insert into dim_goods_info values('g004','苏泊尔(SUPOR)净水器家用超滤软水机');
    insert into dim_goods_info values('g005','小米粽 平板电脑');
    insert into dim_goods_info values('g006','GoPro HERO11 Black运动相机');
    insert into dim_goods_info values('g007','云南实建褚橙冰糖橙');
    insert into dim_goods_info values('g008','四色蓝泡泡洁厕');
    insert into dim_goods_info values('g009','奥康男鞋');
    commit;
    
    -- 国家信息表插入数据
    insert into dim_country_info values('1','美国');
    insert into dim_country_info values('65','新加坡');
    insert into dim_country_info values('81','日本');
    insert into dim_country_info values('61','澳大利亚');
    insert into dim_country_info values('54','阿根廷');
    insert into dim_country_info values('55','巴西');
    insert into dim_country_info values('45','丹麦');
    insert into dim_country_info values('86','中国');
    commit;
    
    -- 省份信息表插入数据
    insert into dim_province_info values('11','北京市','86');
    insert into dim_province_info values('12','天津市','86');
    insert into dim_province_info values('31','上海市','86');
    insert into dim_province_info values('50','重庆市','86');
    insert into dim_province_info values('13','河北省','86');
    insert into dim_province_info values('41','河南省','86');
    insert into dim_province_info values('53','云南省','86');
    insert into dim_province_info values('21','辽宁省','86');
    insert into dim_province_info values('23','湖南省','86');
    insert into dim_province_info values('43','黑龙江省','86');
    insert into dim_province_info values('34','安徽省','86');
    insert into dim_province_info values('37','山东省','86');
    insert into dim_province_info values('65','新疆维吾尔自治区','86');
    insert into dim_province_info values('32','江苏省','86');
    insert into dim_province_info values('33','浙江省','86');
    insert into dim_province_info values('36','江西省','86');
    commit;
    
    -- 城市信息表插入数据
    insert into dim_city_info values('320100','南京市','32');
    insert into dim_city_info values('320200','无锡市','32');
    insert into dim_city_info values('320300','徐州市','32');
    insert into dim_city_info values('320400','常州市','32');
    insert into dim_city_info values('320500','苏州市','32');
    insert into dim_city_info values('320600','南通市','32');
    insert into dim_city_info values('320700','连云港市','32');
    insert into dim_city_info values('320800','淮安市','32');
    insert into dim_city_info values('320900','盐城市','32');
    insert into dim_city_info values('321000','扬州市','32');
    insert into dim_city_info values('321100','镇江市','32');
    insert into dim_city_info values('321200','泰州市','32');
    insert into dim_city_info values('321300','宿迁市','32');
    commit;

![在这里插入图片描述][9358d0135cb7486b83d6476effb435d6.png]  
放入上面 init\_mysql.sql 的sql语句，运行

![在这里插入图片描述][310904f64e24482b8da51cb4df80f36f.png]  
![在这里插入图片描述][f9a3b13c5c91443eae18c430e4cf32c8.png]

点击刷新后创建成功

![在这里插入图片描述][e69741e2f3134f9fb75101b990d924f1.png]

创建result数据库

-- 设置sql_mode
    set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
    
    -- 创建数据库result,并进行切换
    create database result;
    
    use result;
    
    -- 创建城市订单总额表
    CREATE TABLE t_city_sale_total(
    	city_name         varchar(100) not null,
    	city_total_price  int not null
    )DEFAULT CHARSET='utf8';

![在这里插入图片描述][46cee0579d9b446baf5b38d481fdac90.png]

运行 init\_hive.sql 创建mall\_bigdata数据库

--创建数据库mall_bigdata
    create database if not exists mall_bigdata;
    
    --切换数据库至mall_bigdata
    use mall_bigdata;
    
    --创建用户信息表
    create table if not exists mall_bigdata.ods_user_info
    (
    	 user_id       STRING    comment "用户id"
    	,user_name     STRING    comment "用户姓名"
    	,sex           STRING    comment "性别"
    	,age           INT       comment "年龄"
    	,country_code  STRING    comment "国家码"
        ,province_code STRING    comment "省份码"
    	,city_code     STRING    comment "城市码"
    )
    comment "用户信息表"
    row format delimited fields terminated by ","
    stored as textfile;
    
    --创建订单表
    create table if not exists mall_bigdata.ods_sale_order
    (
    	 sale_id       STRING    comment "订单id"
    	,user_id       STRING    comment "用户id"
    	,goods_id      STRING    comment "商品id"
    	,price         INT       comment "单价"
    	,sale_count    INT       comment "购买数量"
        ,total_price   INT       comment "购买总金额"
    	,create_time   STRING    comment "订单生成时间"
    )
    comment "销售订单表"
    row format delimited fields terminated by ","
    stored as textfile;
    
    --创建商品信息表
    create table if not exists mall_bigdata.dim_goods_info
    (
    	 goods_id       STRING    comment "商品id"
    	,goods_name     STRING    comment "商品名称"
    )
    comment "商品信息表"
    row format delimited fields terminated by ","
    stored as textfile;
    
    
    --创建国家信息表
    create table if not exists mall_bigdata.dim_country_info
    (
    	 country_code       STRING    comment "国家码"
    	,country_name       STRING    comment "国家名称"
    )
    comment "国家信息表"
    row format delimited fields terminated by ","
    stored as textfile;
    
    
    --创建省份信息表
    create table if not exists mall_bigdata.dim_province_info
    (
    	 province_code       STRING    comment "省份码"
    	,province_name       STRING    comment "省份名称"
    	,country_code        STRING    comment "国家码"
    )
    comment "省份信息表"
    row format delimited fields terminated by ","
    stored as textfile;
    
    
    --创建城市信息表
    create table if not exists mall_bigdata.dim_city_info
    (
    	 city_code       STRING    comment "城市码"
    	,city_name       STRING    comment "城市名称"
    	,province_code   STRING    comment "省份码"
    )
    comment "城市信息表"
    row format delimited fields terminated by ","
    stored as textfile;

在/opt/file 文件夹下上传或者创建上述的init\_hive.sql文件

![在这里插入图片描述][a334e68bca2f4f8fb80ae259ac5468d8.png]

执行sql文件

> hive -f init\_hive.sql

[8b4d9e8a8caf4c62b7eeaa1195a8597a.png]: https://img-blog.csdnimg.cn/8b4d9e8a8caf4c62b7eeaa1195a8597a.png
[22cd629e9721427580bb7944e7f82eca.png]: https://img-blog.csdnimg.cn/22cd629e9721427580bb7944e7f82eca.png
[8fac7c2bc7da4703bcccbf0b6386483a.png]: https://img-blog.csdnimg.cn/8fac7c2bc7da4703bcccbf0b6386483a.png
[9358d0135cb7486b83d6476effb435d6.png]: https://img-blog.csdnimg.cn/9358d0135cb7486b83d6476effb435d6.png
[310904f64e24482b8da51cb4df80f36f.png]: https://img-blog.csdnimg.cn/310904f64e24482b8da51cb4df80f36f.png
[f9a3b13c5c91443eae18c430e4cf32c8.png]: https://img-blog.csdnimg.cn/f9a3b13c5c91443eae18c430e4cf32c8.png
[e69741e2f3134f9fb75101b990d924f1.png]: https://img-blog.csdnimg.cn/e69741e2f3134f9fb75101b990d924f1.png
[46cee0579d9b446baf5b38d481fdac90.png]: https://img-blog.csdnimg.cn/46cee0579d9b446baf5b38d481fdac90.png
[a334e68bca2f4f8fb80ae259ac5468d8.png]: https://img-blog.csdnimg.cn/a334e68bca2f4f8fb80ae259ac5468d8.png