建表语句如下
-- dim_pro.dim_hospital definition
CREATE TABLE dim_hospital (
hospital_id varchar(150) NULL COMMENT '医院ID',
medical_org_id varchar(300) NULL COMMENT '医疗机构ID',
source_id varchar(300) NULL COMMENT '来源系统ID',
acquisition_time datetime NULL COMMENT '首次数据采集时间',
hospital_name text NULL COMMENT '医院名称',
social_credit_code text NULL COMMENT '社会信用代码',
org_alias text NULL COMMENT '机构别名',
is_branch_hospital int NULL COMMENT '是否是分院',
superior_hospital_code text NULL COMMENT '上级医院代码',
affiliation_code text NULL COMMENT '隶属关系编码',
affiliation_name text NULL COMMENT '隶属关系名称',
org_level_code text NULL COMMENT '机构级别编码',
org_level_name text NULL COMMENT '机构级别名称',
org_grade_code text NULL COMMENT '机构等次编码',
org_grade_name text NULL COMMENT '机构等次名称',
org_nature_code text NULL COMMENT '机构性质编码',
org_nature_name text NULL COMMENT '机构性质名称',
org_contact_telephone text NULL COMMENT '医疗机构联系电话',
legal_person_name text NULL COMMENT '医疗机构负责人（法人）姓名',
legal_person_mobile_phone text NULL COMMENT '医疗机构负责人联系电话',
detailed_address text NULL COMMENT '详细地址',
address_province text NULL COMMENT '地址-省（自治区、直辖市）',
address_city text NULL COMMENT '地址-市（地区、州）',
address_town text NULL COMMENT '地址-县（区）',
address_village text NULL COMMENT '地址-乡（镇、街道办事处）',
address_country text NULL COMMENT '地址-村（街、路、弄等）',
address_house_number text NULL COMMENT '地址-门牌号码',
address_post_code text NULL COMMENT '地址-邮政编码',
archiver_source_id text NULL COMMENT '建档来源系统ID',
delete_flag int NULL COMMENT '删除标志',
create_time datetime NULL COMMENT '业务数据生成时间',
etl_time datetime NULL COMMENT '数据上传日期时间',
source_key text NULL COMMENT '来源系统主键'
) ENGINE=OLAP
UNIQUE KEY(hospital_id, medical_org_id, source_id, acquisition_time)
COMMENT '医院表'
DISTRIBUTED BY HASH(hospital_id, medical_org_id, source_id, acquisition_time) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"binlog.enable" = "false",
"binlog.ttl_seconds" = "86400",
"binlog.max_bytes" = "9223372036854775807",
"binlog.max_history_nums" = "9223372036854775807",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
执行sql如下：
INSERT INTO dim_pro.dim_hospital (hospital_id,medical_org_id,source_id,acquisition_time,hospital_name,social_credit_code,org_alias,is_branch_hospital,superior_hospital_code,affiliation_code,affiliation_name,org_level_code,org_level_name,org_grade_code,org_grade_name,org_nature_code,org_nature_name,org_contact_telephone,legal_person_name,legal_person_mobile_phone,detailed_address,address_province,address_city,address_town,address_village,address_country,address_house_number,address_post_code,archiver_source_id,delete_flag,create_time,etl_time,source_key) VALUES
('1321710484659855361','1321710484659855361','23','2024-06-12 21:25:31','深圳宝兴医院',NULL,NULL,0,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'深圳',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'23',0,'2024-06-12 21:25:31','2024-08-27 11:45:47','3551'),
('1328624381060960258','1328624381060960258','23','2024-06-12 21:25:31','深圳新宝兴血液透析中心',NULL,NULL,0,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'35',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'23',0,'2024-06-12 21:25:31','2024-08-27 11:45:47','6109');
doris版本2.1.6
