建表语句如下
-- 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