1 Answers
sql 已经找到，SELECT * FROM hive.ods_test.ods_tiktok_order_coupons_record_i，hive建表语句
CREATE TABLE ${db_ods}.ods_tiktok_order_coupons_record_i (
-- 存储优惠券相关信息
coupons STRUCT<
certificates_v2: ARRAY< -- certificates_v2数组，包含多个优惠券信息
STRUCT<
encrypted_code: STRING, -- 加密的优惠券代码
amount: STRUCT< -- 优惠券金额相关信息
coupon_pay_amount: BIGINT, -- 用户支付的优惠券金额
platform_discount_amount: BIGINT, -- 平台折扣金额
pay_amount: BIGINT, -- 实际支付金额
merchant_ticket_amount: BIGINT, -- 商家优惠金额
original_amount: BIGINT, -- 优惠券的原始金额
payment_discount_amount: BIGINT, -- 支付时的折扣金额,
list_market_amount:BIGINT
>,
expire_time: BIGINT, -- 优惠券到期时间 (Unix 时间戳)
book_info: STRUCT<
book_poi_id: STRING -- 预定商家的ID
>,
certificate_id: BIGINT, -- 优惠券ID
start_time: BIGINT, -- 优惠券开始时间 (Unix 时间戳)
sku: STRUCT< -- SKU（库存单位）相关信息
third_sku_id: STRING, -- 第三方SKU ID
sku_id: STRING, -- SKU ID
title: STRING, -- 优惠券标题
account_id: STRING, -- 账户ID
sold_start_time: BIGINT, -- 优惠券销售开始时间 (Unix 时间戳)
groupon_type: INT, -- 团购类型标识
market_price: BIGINT -- 市场价格
>,
use_time_info: STRUCT<
use_time_type: INT -- 使用时间类型
>,
status: INT -- 优惠券状态
>
>,
description: STRING, -- 优惠券描述信息
certificates: ARRAY< -- certificates数组，包含多个优惠券信息
STRUCT<
amount: STRUCT<
coupon_pay_amount: BIGINT, -- 用户支付的优惠券金额
platform_discount_amount: BIGINT, -- 平台折扣金额
pay_amount: BIGINT, -- 实际支付金额
merchant_ticket_amount: BIGINT, -- 商家优惠金额
original_amount: BIGINT, -- 优惠券的原始金额
payment_discount_amount: BIGINT, -- 支付时的折扣金额
list_market_amount:BIGINT
>,
encrypted_code: STRING, -- 加密的优惠券代码
expire_time: BIGINT, -- 优惠券到期时间 (Unix 时间戳)
book_info: STRUCT<
book_poi_id: STRING -- 预定商家的ID
>,
certificate_id: BIGINT, -- 优惠券ID
start_time: BIGINT, -- 优惠券开始时间 (Unix 时间戳)
sku: STRUCT< -- SKU（库存单位）相关信息
third_sku_id: STRING, -- 第三方SKU ID
sku_id: STRING, -- SKU ID
title: STRING, -- 优惠券标题
account_id: STRING, -- 账户ID
sold_start_time: BIGINT, -- 优惠券销售开始时间 (Unix 时间戳)
groupon_type: INT, -- 团购类型标识
market_price: BIGINT -- 市场价格
>,
use_time_info: STRUCT<
use_time_type: INT -- 使用时间类型
>,
status: INT -- 优惠券状态
>
>,
error_code: INT -- 错误代码
>,
-- 存储订单相关信息
order STRUCT<
open_id: STRING, -- 用户的 OpenID
discount_amount: BIGINT, -- 折扣金额
pay_amount: BIGINT, -- 支付金额
count: INT, -- 商品数量
intention_poi_id: STRING, -- 商家ID
certificate: ARRAY< -- 证书/凭证信息数组
STRUCT<
combination_id: STRING, -- 组合ID
item_status: INT, -- 商品状态
certificate_id: STRING, -- 证书ID
order_item_id: STRING, -- 订单项ID
refund_time: BIGINT, -- 退款时间 (Unix 时间戳)
refund_amount: BIGINT, -- 退款金额
item_update_time: BIGINT -- 项目更新时间 (Unix 时间戳)
>
>,
create_order_time: BIGINT, -- 订单创建时间 (Unix 时间戳)
third_sku_id: STRING, -- 第三方SKU ID
sku_id: STRING, -- SKU ID
pay_time: BIGINT, -- 支付时间 (Unix 时间戳)
products: ARRAY< -- 产品信息数组
STRUCT<
num: INT, -- 产品数量
commodities: STRING, -- 商品信息
sku_id: STRING, -- SKU ID
snapshot_product: STRUCT< -- 产品快照信息
product_attr: STRUCT<
show_channel: INT -- 显示渠道
>,
tag_info_list: STRING -- 标签信息
>,
product_name: STRING, -- 产品名称
product_id: STRING -- 产品ID
>
>,
update_order_time: BIGINT, -- 订单更新时间 (Unix 时间戳)
order_status: INT, -- 订单状态
sub_order_amount_infos: ARRAY< -- 子订单金额信息数组
STRUCT<
combination_id: STRING, -- 组合ID
discount_amount: BIGINT, -- 折扣金额
pay_amount: BIGINT, -- 支付金额
sub_order_id: STRING, -- 子订单ID
origin_amount: BIGINT, -- 原始金额
sub_order_type: INT, -- 子订单类型
discounts: ARRAY< -- 折扣信息数组
STRUCT<
platform_discount_amount: BIGINT, -- 平台折扣金额
discount_amount: BIGINT, -- 折扣金额
merchant_discount_amount: BIGINT, -- 商家折扣金额
discount_type: INT, -- 折扣类型
brand_discount_amount: BIGINT, -- 品牌折扣金额
provider_discount_amount: BIGINT -- 供应商折扣金额
>
>,
receipt_amount: BIGINT -- 收据金额
>
>,
original_amount: BIGINT, -- 原始金额
payment_discount: BIGINT, -- 支付折扣
receipt_amount: BIGINT, -- 收据金额
sku_name: STRING, -- SKU 名称
poi_id: STRING, -- 商家ID
order_id: STRING, -- 订单ID
order_type: INT, -- 订单类型
contacts: ARRAY< -- 联系人信息数组
STRUCT<
phone: STRING, -- 电话号码
name: STRING -- 姓名
>
>
>
)
-- 分区字段，用于存储ETL日期
PARTITIONED BY (etl_date STRING)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;