跳转至

第2章 数据表

1 创建数据库TestDB并查看数据库
CREATE DATABASE IF NOT EXISTS TestDB;
SHOW DATABASES;
2 创建内部表employees
CREATE TABLE IF NOT EXISTS TestDB.employees(
name STRING COMMENT 'Employee name',
age INT,
salary FLOAT COMMENT 'Employee salary',
department STRING,
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING, FLOAT>  );
3 创建外部表employees_external
CREATE  EXTERNAL  TABLE IF NOT EXISTS TestDB.employees_external(
name STRING COMMENT 'Employee name',
age INT,
salary FLOAT COMMENT 'Employee salary',
department STRING,
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING, FLOAT>  )
LOCATION  '/ext/';
4 创建静态分区表employees_partition
CREATE TABLE IF NOT EXISTS TestDB.employees_partition(
name  STRING  COMMENT  'Employee name',
age INT,
salary FLOAT COMMENT 'Employee salary',
department STRING,
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING, FLOAT>  )
PARTITIONED BY (country STRING, state STRING);
5 创建动态分区表employees_partition_dynamic
set hive.exec.dynamic.partition=true;  // 开启动态分区模式
set hive.exec.dynamic.partition.mode=nostrict;  // 设置动态分区模式
CREATE TABLE IF NOT EXISTS TestDB.employees_partition_dynamic(
name  STRING  COMMENT  'Employee name',
age INT,
salary FLOAT COMMENT 'Employee salary',
department STRING,
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING, FLOAT>  )
PARTITIONED BY (country STRING, state STRING);
6 创建桶表employees_cluster
CREATE TABLE IF NOT EXISTS TestDB.employees_cluster(
name  STRING  COMMENT  'Employee name',
age INT,
salary FLOAT COMMENT 'Employee salary',
department STRING,
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING, FLOAT>  )
CLUSTERED BY  (department ) SORTED BY (age DESC)  INTO 3 BUCKETS;
7 创建临时表Course_Temp
CREATE TEMPORARY TABLE TestDB.Course_Temp(id INT,course STRING)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY','
  LINES TERMINATED BY '\n'
  STORED AS textfile;
8 查看employees表的表结构信息
DESCRIBE  TestDB.employees;
9 创建log_message表
USE TestDB;
CREATE EXTERNAL TABLE IF NOT EXISTS  log_message (
hms INT,
severity STRING,
server STRING,
process_id INT,
message STRING)
PARTITIONED  BY  (year INT,  month  INT , day  INT)
ROW FORMAT DELIMITED FIELDS TERMINATED  BY  '\t';
10 表重命名
ALTER TABLE  log_message  RENAME  TO  log_message_new ;
11 增加新的分区
ALTER TABLE log_message_new  ADD IF NOT EXISTS
  PARTITION(year = 2022, month = 1,day = 1 ) LOCATION '/user/hive/warehouse/testdb.db/log_message_new/2022/01/01'
  PARTITION(year = 2022, month = 1,day = 2 ) LOCATION '/user/hive/warehouse/testdb.db/log_message_new/2022/01/02'
  PARTITION(year = 2022, month = 1,day = 3 ) LOCATION '/user/hive/warehouse/testdb.db/log_message_new/2022/01/03';

erDiagram
    mediamatch_usermsg_用户基础信息表 {
        string terminal_no UK "终端号"
        string phone_no PK "电话号码"
        string sm_name "SM名称"
        string run_name "运行名称"
        string sm_code "SM编码"
        string owner_name "业主名称"
        string owner_code "业主编码"
        string run_time "运行时间"
        string addressoj "地址"
        string open_time "开通时间"
        string force "强制标志"
    }

    mediamatch_userevent_用户状态变更表 {
        string phone_no FK "电话号码"
        string run_name "运行名称"
        string run_time "运行时间"
        string owner_name "业主名称"
        string owner_code "业主编码"
        string open_time "开通时间"
        string sm_name "SM名称"
    }

    mmconsume_billevents_账单数据表 {
        string terminal_no FK "终端号"
        string phone_no PK, FK "电话号码"
        string fee_code PK "费用编码"
        string year_month PK "账期"
        string owner_name "业主名称"
        string owner_code "业主编码"
        string sm_name "SM名称"
        string should_pay "应缴金额"
        string favour_fee "优惠金额"
    }

    order_index_订单数据表 {
        string phone_no FK "电话号码"
        string owner_name "业主名称"
        string optdate "操作日期"
        string prodname "产品名称"
        string sm_name "SM名称"
        string offerid "报价ID"
        string offername "报价名称"
        string business_name "业务名称"
        string owner_code "业主编码"
        string prodprcid "产品价格ID"
        string prodprcname "产品价格名称"
        string effdate "生效日期"
        string expdate "失效日期"
        string orderdate "订单日期"
        string cost "费用"
        string mode_time "模式时间"
        string prodstatus "产品状态"
        string run_name "运行名称"
        string orderno PK "订单号"
        string offertype "报价类型"
    }

    media_index_用户收视行为数据表 {
        string terminal_no FK "终端号"
        string phone_no FK "电话号码"
        string duration "时长"
        string station_name "频道名称"
        string origin_time "开始时间"
        string end_time "结束时间"
        string owner_code "业主编码"
        string owner_name "业主名称"
        array vod_cat_tags "VOD分类标签复杂结构"
        string resolution "分辨率"
        string audio_lang "音频语言"
        string region "区域"
        string res_name "资源名称"
        string res_type "资源类型"
        string vod_title "VOD标题"
        string category_name "分类名称"
        string program_title "节目名称"
        string sm_name "SM名称"
    }

    mediamatch_usermsg_用户基础信息表 ||--o{ mediamatch_userevent_用户状态变更表 : "phone_no"
    mediamatch_usermsg_用户基础信息表 ||--o{ mmconsume_billevents_账单数据表 : "phone_no / terminal_no"
    mediamatch_usermsg_用户基础信息表 ||--o{ order_index_订单数据表 : "phone_no"
    mediamatch_usermsg_用户基础信息表 ||--o{ media_index_用户收视行为数据表 : "phone_no / terminal_no"
12 创建数据库ZJSM
CREATE DATABASE IF NOT EXISTS ZJSM;
13 创建用户基本数据表
USE ZJSM;
CREATE TABLE IF NOT EXISTS mediamatch_usermsg(
terminal_no STRING,
phone_no STRING,
sm_name STRING,
run_name STRING,
sm_code STRING,
owner_name STRING,
owner_code STRING,
run_time STRING,
addressoj STRING,
open_time STRING,
force STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;';
14 创建用户状态变更数据表
CREATE TABLE IF NOT EXISTS mediamatch_userevent(
phone_no STRING,
run_name STRING,
run_time STRING,
owner_name STRING,
owner_code STRING,
open_time STRING,
sm_name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;';
15 创建账单数据表
CREATE TABLE IF NOT EXISTS mmconsume_billevents(
terminal_no STRING,
phone_no STRING,
fee_code STRING,
year_month STRING,
owner_name STRING,
owner_code STRING,
sm_name STRING,
should_pay STRING,
favour_fee STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;';
16 创建订单数据表
CREATE TABLE IF NOT EXISTS order_index(
phone_no STRING,
owner_name STRING,
optdate STRING,
prodname STRING,
sm_name STRING,
offerid STRING,
offername STRING,
business_name STRING,
owner_code STRING,
prodprcid STRING,
prodprcname STRING,
effdate STRING,
expdate STRING,
orderdate STRING,
cost STRING,
mode_time STRING,
prodstatus STRING,
run_name STRING,
orderno STRING,
offertype STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;';
17 创建用户收视行为数据表
CREATE TABLE IF NOT EXISTS media_index(
terminal_no STRING,
phone_no STRING,
duration STRING,
station_name STRING,
origin_time STRING,
end_time STRING,
owner_code STRING,
owner_name STRING,
vod_cat_tags ARRAY<STRUCT<level1_name: STRING,level2_name: STRING,level3_name: STRING,level4_name: STRING, level5_name: STRING>>,
resolution STRING,
audio_lang STRING,
region STRING,
res_name STRING,
res_type STRING,
vod_title STRING,
category_name STRING,
program_title STRING,
sm_name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;';