> cd /home/work/warehouse_goods_order
> vi ods_shopmall_init.sh

#!/bin/bash
# ods层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists ods_shopmall;

create external table if not exists ods_shopmall.ods_user (
    id              bigint,
    username        string,
    gender          tinyint,
    birthday        string,
    email           string,
    mobile          string,
    createtime      string,
    disabled        tinyint
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/user/';

create external table if not exists ods_shopmall.ods_user_addr (
    id              bigint,
    userid          bigint,
    addrname        string,
    isdefault       tinyint,
    username        string,
    mobile          string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/user_addr/';

create external table if not exists ods_shopmall.ods_category (
    id              int,
    parentids       string,
    name            string,
    level           int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/category/';

create external table if not exists ods_shopmall.ods_goods_info (
    id              bigint,
    name            string,
    desc            string,
    price           double,
    categoryid      int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/goods_info/';

create external table if not exists ods_shopmall.ods_payment (
    id              bigint,
    orderid         bigint,
    tradeno         string,
    money           double,
    type            int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/payment/';

create external table if not exists ods_shopmall.ods_order (
    id              bigint,
    userid          bigint,
    money           double,
    type            int,
    status          int,
    payid           bigint,
    createtime      string
    update_time     string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/order/';

create external table if not exists ods_shopmall.ods_orderitem (
    orderid         bigint,
    goodsid         bigint,
    amount          int,
    price           double,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/orderitem/';
"

> cd /home/work/hive-3.1.3
> ./bin/hive

# 先创建数据库
hive> create database if not exists ods_shopmall;

# 再创建数据表
hive> create external table if not exists ods_shopmall.ods_app_open (
          log    string
      ) partitioned by (dt string)
        row format delimited
        fields terminated by '\t'
        location 'hdfs://server01:9000/data/ods/ua/';

hive> create external table if not exists ods_shopmall.ods_goods_click (
          log    string
      ) partitioned by (dt string)
        row format delimited
        fields terminated by '\t'
        location 'hdfs://server01:9000/data/ods/ua/';

hive> create external table if not exists ods_shopmall.ods_goods_item (
          log    string
      ) partitioned by (dt string)
        row format delimited
        fields terminated by '\t'
        location 'hdfs://server01:9000/data/ods/ua/';

hive> create external table if not exists ods_shopmall.ods_goods_list (
          log    string
      ) partitioned by (dt string)
        row format delimited
        fields terminated by '\t'
        location 'hdfs://server01:9000/data/ods/ua/';

hive> create external table if not exists ods_shopmall.ods_app_collapse (
          log    string
      ) partitioned by (dt string)
        row format delimited
        fields terminated by '\t'
        location 'hdfs://server01:9000/data/ods/ua/';

# 给表增加分区
hive> alter table ods_shopmall.ods_app_open add if not exists partition(dt = '20240518') location '20240518/1';

# 查询数据
hive> select * from ods_shopmall.ods_app_open where dt = '20240518';