create database db;
create table if not exists db.session_record(
    `common_recv_time` bigint comment '接收时间, Unix timestamp',
    `common_log_id` bigint comment '日志ID',
    `common_stream_trace_id` bigint comment '流的追踪ID',
    `common_direction` bigint comment '传输方向',
    `common_stream_dir` bigint comment '流类型',
    `common_start_time` bigint comment '会话开始时间, Unix timestamp',
    `common_end_time` bigint comment '会话结束时间, Unix timestamp',
    `common_con_duration_ms` bigint comment '通信时长',
    `common_establish_latency_ms` bigint comment '会话创建延迟',
    `common_processing_time` bigint comment '处理时间, OLAP处理时间',
    `common_insert_time` bigint comment '存储时间, 写入到数据库时间',
    `common_ingestion_time` bigint comment '摄入时间, 存储到Kafka时间',
    `common_entrance_id` bigint comment '出入口编号',
    `common_device_id` string comment '设备编号',
    `common_out_link_id` bigint comment '出方向链路号',
    `common_in_link_id` bigint comment '入方向链路号',
    `common_isp` string comment '运营商',
    `common_data_center` string comment '数据中心',
    `common_sled_ip` string comment '处理机IP',
    `common_device_group` string comment '设备组',
    `common_app_behavior` string comment 'App行为',
    `common_action` bigint comment '动作',
    `common_sub_action` string comment '子动作',
    `common_policy_id` bigint comment '策略ID',
    `common_user_tags` string comment '标签',
    `common_user_region` string comment '用户自定义域',
    `common_shaping_rule_ids` array<bigint> comment '整形规则ID列表',
    `common_client_ip` string comment '客户端ip地址',
    `common_internal_ip` string comment '域内IP',
    `common_client_port` bigint comment '客户端端口',
    `common_client_location` string comment '客户端地址定位信息',
    `common_client_asn` string comment '客户端ASN',
    `common_subscriber_id` string comment '客户端用户名',
    `common_imei` string comment '设备标识IMEI',
    `common_imsi` string comment '用户标识IMSI',
    `common_phone_number` string comment '手机号Phone Number',
    `common_in_src_mac` string comment 'External到Internal流量源MAC地址',
    `common_out_src_mac` string comment 'Internal到External流量源MAC地址',
    `common_server_ip` string comment '服务端ip地址',
    `common_external_ip` string comment '域外 IP',
    `common_server_port` bigint comment '服务端端口',
    `common_server_location` string comment '服务端地址定位信息',
    `common_server_asn` string comment '服务端ASN',
    `common_server_fqdn` string comment '服务端FQDN',
    `common_server_domain` string comment '服务端域名',
    `common_in_dest_mac` string comment 'External到Internal流量目的MAC地址',
    `common_out_dest_mac` string comment 'Internal到External流量目的MAC地址',
    `common_app_id` string comment 'APP ID',
    `common_app_full_path` string comment 'APP 完整识别路径',
    `common_userdefine_app_name` string comment '用户自定义App Name',
    `common_app_identify_info` string comment 'APP识别信息',
    `common_app_label` string comment 'APP标签',
    `common_app_surrogate_id` string comment 'surrogate标识ID',
    `common_l7_protocol` string comment '应用层协议',
    `common_protocol_label` string comment '协议特征标签',
    `common_service_category` array<bigint> comment 'FQDN服务分类',
    `common_service` bigint comment '业务类型',
    `common_l4_protocol` string comment '协议类型',
    `common_sessions` bigint comment '会话数量',
    `common_c2s_pkt_num` bigint comment 'c2s包数(累积)',
    `common_s2c_pkt_num` bigint comment 's2c包数(累积)',
    `common_c2s_pkt_diff` bigint comment 'c2s包数(差值)',
    `common_s2c_pkt_diff` bigint comment 's2c包数(差值)',
    `common_c2s_byte_num` bigint comment 'c2s字节数(累积)',
    `common_s2c_byte_num` bigint comment 's2c字节数(累积)',
    `common_c2s_byte_diff` bigint comment 'c2s字节数(差值)',
    `common_s2c_byte_diff` bigint comment 's2c字节数(差值)',
    `common_c2s_ipfrag_num` bigint comment 'c2s侧IP分片包数量',
    `common_s2c_ipfrag_num` bigint comment 's2c侧IP分片包数量',
    `common_c2s_tcp_lostlen` bigint comment 'c2s侧TCP流量缺失长度',
    `common_s2c_tcp_lostlen` bigint comment 's2c侧TCP流量缺失长度',
    `common_c2s_tcp_unorder_num` bigint comment 'c2s侧TCP乱序包数量',
    `common_s2c_tcp_unorder_num` bigint comment 's2c侧TCP乱序包数量',
    `common_c2s_pkt_retrans` bigint comment 'c2s重传包数',
    `common_s2c_pkt_retrans` bigint comment 's2c重传包数',
    `common_c2s_byte_retrans` bigint comment 'c2s重传字节数',
    `common_s2c_byte_retrans` bigint comment 's2c重传字节数',
    `common_first_ttl` bigint comment '首包TTL',
    `common_tcp_client_isn` bigint comment '客户端初始序列号',
    `common_tcp_server_isn` bigint comment '服务端初始序列号',
    `common_mirrored_pkts` bigint comment '镜像流量转发包数',
    `common_mirrored_bytes` bigint comment '镜像流量转发字节数',
    `common_address_type` bigint comment '地址类型',
    `common_schema_type` string comment '日志类型',
    `common_vsys_id` bigint comment '所属vsys',
    `common_t_vsys_id` bigint comment '所属流量vsys',
    `common_flags` bigint comment '会话属性标记',
    `common_flags_identify_info` string comment '会话属性标记识别信息',
    `common_device_tag` string comment '设备标签',
    `common_encapsulation` bigint comment '原始二层封装格式',
    `common_tunnels` string comment '隧道信息',
    `common_address_list` string comment '嵌套地址列表',
    `common_has_dup_traffic` bigint comment '是否有重复流量',
    `common_stream_error` string comment '异常流的种类',
    `common_link_info_c2s` string comment '原始链路层信息',
    `common_link_info_s2c` string comment '原始链路层信息',
    `common_packet_capture_file` string comment '原始包存储路径',
    `common_tunnel_endpoint_a_desc` string comment 'Endpoint A 描述信息',
    `common_tunnel_endpoint_b_desc` string comment 'Endpoint B 描述信息'
)
comment 'session_record'
partitioned by (`date` string)
row format delimited
fields terminated by '\t'
stored as orc
;