clickhouse命令1-create table sql

注意：ON CLUSTER perftest_2shards_1replicas
人脸对象：CREATE TABLE v_face ( face_id String, info_kind Nullable(UInt8), source_id Nullable(String), device_id Nullable(String), left_top_x Nullable(UInt16), left_top_y Nullable(UInt16), right_btm_x Nullable(UInt16), right_btm_y Nullable(UInt16), is_driver Nullable(UInt8), is_foreigner Nullable(UInt8), is_suspected_terrorist Nullable(UInt8), is_criminal_involved Nullable(UInt8), is_detainees Nullable(UInt8), is_victim Nullable(UInt8), is_suspicious_person Nullable(UInt8), location_mark_time Nullable(datetime), face_appear_time Nullable(datetime), face_disAppear_time Nullable(datetime), id_type Nullable(UInt8), id_number Nullable(UInt32), name Nullable(String), used_name Nullable(String), alias Nullable(String), gender_code Nullable(String), age_up_limit Nullable(UInt8), age_lower_limit Nullable(UInt8), ethic_code Nullable(String), natio_nality_code Nullable(String), native_city_code Nullable(String), residence_admin_division Nullable(String), chinese_accent_code Nullable(String), job_category Nullable(String), accompany_number Nullable(UInt32), hair_style Nullable(String), hair_color Nullable(String), skin_color Nullable(String), face_style Nullable(String), facial_feature Nullable(String), physical_feature Nullable(String), respirator_color Nullable(String), cap_style Nullable(String), cap_color Nullable(String), glasses_style Nullable(String), glasses_color Nullable(String), passport_type Nullable(String), immigrant_type_code Nullable(String), suspected_terroris_tnumber Nullable(UInt16), criminal_involved_specilisation_code Nullable(UInt16), body_speciall_mark Nullable(String), crime_method Nullable(String), crime_character_code Nullable(String), escaped_criminal_number Nullable(String), detention_house_code Nullable(String), detainees_identity Nullable(String), detainees_special_identity Nullable(String), member_type_code Nullable(String), victim_type Nullable(String), injured_degree Nullable(String), corpse_condition_code Nullable(String), attitude Nullable(UInt16), similarity_degree Nullable(Float64), eyebrow_style Nullable(String), nose_style Nullable(String), mustache_style Nullable(String), lip_style Nullable(String), wrinkle_pouch Nullable(String), acnestain Nullable(String), freckle_birth_mark Nullable(String), scard_imple Nullable(String), other_feature Nullable(String), data_flg Nullable(UInt16), crt_time UInt64, crt_user Nullable(String), crt_name Nullable(String), crt_host Nullable(String), upd_time Nullable(UInt64), upd_user Nullable(String), upd_name Nullable(String), upd_host Nullable(String) )ENGINE MergeTree() primary key face_id partition BY toYYYYMMDD(FROM_UNIXTIME(crt_time)) ORDER BY (face_id,crt_time) SETTINGS index_granularity=8192;

CREATE TABLE v_face_all AS v_face ENGINE = Distributed(perftest_2shards_1replicas, default, v_face, rand());

图像对象：CREATE TABLE v_image_info ( image_id String, info_kind Nullable(UInt8) , image_source Nullable(String) , source_video_id Nullable(String) , origin_Image_id Nullable(String), event_sort Nullable(UInt32) , device_id Nullable(String) , storage_path Nullable(String) , file_hash Nullable(String) , file_format Nullable(String) , shot_time Nullable(datetime) , title Nullable(String) , title_note Nullable(String) , specialI_name Nullable(String) , keyword Nullable(String) , content_description Nullable(String) , subject_character Nullable(String) , shot_place_code Nullable(String) , shot_place_pull_address Nullable(String) , shot_place_longitude Nullable(Float64) , shot_place_latitude Nullable(Float64) , horizontal_shot_direction Nullable(String) , vertical_shot_direction Nullable(String) , security_level Nullable(String) , width Nullable(UInt32) , height Nullable(UInt32) , camera_manufacturer Nullable(String) , camera_version Nullable(String) , aperture_value Nullable(UInt32) , iso_sensitivity Nullable(UInt32) , focal_length Nullable(Float64) , quality_grade Nullable(String) , collector_name Nullable(String) , collector_org Nullable(String), collector_id_Type Nullable(UInt8) , collector_id Nullable(UInt32) , entry_clerk Nullable(String) , entry_clerk_org Nullable(String) , entry_clerk_id_type Nullable(UInt8) , entry_clerk_id Nullable(UInt32) , entry_time Nullable(datetime) , image_proc_flag Nullable(UInt8) , file_size Nullable(UInt32) , father_id Nullable(String) , image_type Nullable(String) , data_flg Nullable(UInt8) , crt_time UInt64 , crt_user Nullable(String) , crt_name Nullable(String) , crt_host Nullable(String) , upd_time Nullable(UInt64) , upd_name Nullable(String) , upd_user Nullable(String) , upd_host Nullable(String) ) ENGINE MergeTree() primary key image_id partition BY toYYYYMMDD(FROM_UNIXTIME(crt_time)) ORDER BY (image_id,crt_time) SETTINGS index_granularity=8192;

CREATE TABLE v_image_info_all AS v_image_info ENGINE = Distributed(perftest_2shards_1replicas, default, v_image_info, rand());

人员对象： CREATE TABLE v_person on cluster perftest_2shards_1replicas( person_id String, info_kind Nullable(UInt8), source_id Nullable(String), device_id Nullable(String), left_top_x Nullable(UInt32), left_top_y Nullable(UInt32), right_btm_x Nullable(UInt32), right_btm_y Nullable(UInt32), is_driver Nullable(UInt8), is_foreigner Nullable(UInt8), is_suspected_terrorist Nullable(UInt8), is_criminal_involved Nullable(UInt8), is_detainees Nullable(UInt8), is_victim Nullable(UInt8), is_suspicious_person Nullable(UInt8), location_mark_time Nullable(datetime), person_appear_time Nullable(datetime), person_tisAppear_time Nullable(datetime), id_type Nullable(UInt8), id_number Nullable(UInt32), name Nullable(String), used_name Nullable(String), alias Nullable(String), gender_code Nullable(String), age_up_limit Nullable(UInt8), age_lower_limit Nullable(UInt8), ethic_code Nullable(String), natio_nality_code Nullable(String), native_city_code Nullable(String), residence_admin_division Nullable(String), chinese_accent_code Nullable(String), person_org Nullable(String), job_category Nullable(String), accompany_number Nullable(UInt32), hair_style Nullable(String), hair_color Nullable(String), skin_color Nullable(String), gesture Nullable(String), status Nullable(String), height_up_limit Nullable(UInt16), height_lower_limit Nullable(UInt16), body_type Nullable(String), face_style Nullable(String), facial_feature Nullable(String), physical_feature Nullable(String), body_feature Nullable(String), habitual_movement Nullable(String), behavior Nullable(String), behavior_description Nullable(String), appendant Nullable(String), appendant_description Nullable(String), umbrella_color Nullable(String), respirator_color Nullable(String), cap_style Nullable(String), cap_color Nullable(String), glasses_style Nullable(String), glasses_color Nullable(String), scarf_color Nullable(String), bag_style Nullable(String), bag_color Nullable(String), coat_style Nullable(String), coat_length Nullable(String), coat_color Nullable(String), trousers_style Nullable(String), trousers_color Nullable(String), trousers_len Nullable(String), shoes_style Nullable(String), shoes_color Nullable(String), passport_type Nullable(String), immigrant_type_code Nullable(String), suspected_terroris_tnumber Nullable(UInt16),
criminal_involved_specilisation_code Nullable(UInt16),
body_speciall_mark Nullable(String), crime_method Nullable(String), crime_character_code Nullable(String), escaped_criminal_number Nullable(String), detention_house_code Nullable(String), detainees_identity Nullable(String), detainees_special_identity Nullable(String), member_type_code Nullable(String),
victim_type Nullable(String),
injured_degree Nullable(String), corpse_condition_code Nullable(String),
attitude Nullable(UInt16),
similarity_degree Nullable(Float64), eyebrow_style Nullable(String), nose_style Nullable(String), mustache_style Nullable(String), lip_style Nullable(String), wrinkle_pouch Nullable(String), acnestain Nullable(String), freckle_birth_mark Nullable(String), scard_imple Nullable(String), other_feature Nullable(String), data_flg Nullable(UInt8), crt_time UInt64, crt_user Nullable(String),
crt_name Nullable(String),
crt_host Nullable(String), upd_time Nullable(UInt64), upd_user Nullable(String),
upd_name Nullable(String), upd_host Nullable(String) ) ENGINE MergeTree() primary key person_id partition BY toYYYYMMDD(FROM_UNIXTIME(crt_time)) ORDER BY (person_id,crt_time) SETTINGS index_granularity=8192;

CREATE TABLE v_person_all on cluster perftest_2shards_1replicas AS v_person ENGINE = Distributed(perftest_2shards_1replicas, default, v_person, rand());

机动车对象： CREATE TABLE v_motor_vehicle on cluster perftest_2shards_1replicas( motor_vehicle_id String, info_kind Nullable(UInt8), source_id Nullable(String), tollgate_id Nullable(String) , device_id Nullable(String) , storage_url1 Nullable(String), storage_url2 Nullable(String), storage_url3 Nullable(String), storage_url4 Nullable(String), storage_url5 Nullable(String), left_top_x Nullable(UInt32), left_top_y Nullable(UInt32), right_btm_x Nullable(UInt32), right_btm_y Nullable(UInt32), mark_time Nullable(datetime), appear_time Nullable(datetime), disappear_time Nullable(datetime), lane_no Nullable(UInt32), has_plate Nullable(UInt8) , plate_class Nullable(String), plate_color Nullable(String), plate_no Nullable(String), plate_no_attach Nullable(String) , plate_describe Nullable(String) , is_decked Nullable(UInt8) , is_altered Nullable(UInt8) , is_covered Nullable(UInt8) , speed Nullable(Float32), direction Nullable(String), driving_status_code Nullable(String), using_properties_code Nullable(UInt32), vehicle_class Nullable(String), vehicle_brand Nullable(String) , vehicle_model Nullable(String) , vehicle_styles Nullable(String) , vehicle_length Nullable(UInt8), vehicle_width Nullable(UInt8), vehicle_height Nullable(UInt8), vehicle_color Nullable(String), vehicle_color_depth Nullable(String), vehicle_hood Nullable(String), vehicle_trunk Nullable(String), vehicle_wheel Nullable(String) , wheel_printed_pattern Nullable(String) , vehicle_window Nullable(String) , vehicle_roof Nullable(String) , vehicle_door Nullable(String) , side_of_vehicle Nullable(String) , car_of_vehicle Nullable(String) , rearview_mirror Nullable(String) , vehicle_chassis Nullable(String) , vehicle_shielding Nullable(String) , film_color Nullable(String), is_modified Nullable(UInt8) , hit_mark_info Nullable(String), vehicle_body_desc Nullable(String) , vehicle_front_item Nullable(String), desc_of_front_item Nullable(String), vehicle_rearItem Nullable(String), desc_of_rear_item Nullable(String), num_of_passenger Nullable(UInt32) , pass_time Nullable(datetime) , name_of_passed_road Nullable(String) , is_suspicious Nullable(UInt8) , sunvisor Nullable(UInt8), safety_belt Nullable(UInt8), calling Nullable(UInt8), plate_reliability Nullable(String), plate_char_reliability Nullable(String), brand_reliability Nullable(String), data_flg Nullable(UInt8) , crt_time UInt64 , crt_user Nullable(String) , crt_name Nullable(String) , crt_host Nullable(String) , upd_time Nullable(UInt64) , upd_user Nullable(String), upd_name Nullable(String) , upd_host Nullable(String) ) ENGINE MergeTree() primary key motor_vehicle_id partition BY toYYYYMMDD(FROM_UNIXTIME(crt_time)) ORDER BY (motor_vehicle_id,crt_time) SETTINGS index_granularity=8192;

CREATE TABLE v_motor_vehicle_all on cluster perftest_2shards_1replicas AS v_motor_vehicle ENGINE = Distributed(perftest_2shards_1replicas, default, v_motor_vehicle, rand());

非机动车：CREATE TABLE v_non_motor_vehicle on cluster perftest_2shards_1replicas( non_motor_vehicle_id String, info_kind Nullable(UInt8) , source_id Nullable(String) , device_id Nullable(String) , left_top_x Nullable(UInt32) , left_top_y Nullable(UInt32), right_btm_x Nullable(UInt32), right_btm_y Nullable(UInt32), mark_time Nullable(datetime) , appear_time Nullable(datetime) , disappear_time Nullable(datetime) , has_plate Nullable(UInt8) , plate_class Nullable(String), plate_color Nullable(String), plate_no Nullable(String) , plate_no_attach Nullable(String), plate_describe Nullable(String), is_decked Nullable(UInt8) , is_altered Nullable(UInt8) , is_covered Nullable(UInt8) , speed Nullable(Float32), driving_status_code Nullable(String), using_properties_code Nullable(UInt8) , vehicle_brand Nullable(String) , vehicle_type Nullable(String), vehicle_length Nullable(UInt8), vehicle_width Nullable(UInt8), vehicle_height Nullable(UInt8), vehicle_color Nullable(String) , vehicle_hood Nullable(String), vehicle_trunk Nullable(String) , vehicle_wheel Nullable(String) , wheel_printed_pattern Nullable(String), vehicle_window Nullable(String) , vehicle_roof Nullable(String) , vehicle_door Nullable(String) , side_of_vehicle Nullable(String) , car_of_vehicle Nullable(String) , rearview_mirror Nullable(String) , vehicle_chassis Nullable(String) , vehicle_shielding Nullable(String) , film_color Nullable(String), is_modified Nullable(UInt8), data_flg Nullable(UInt8) , crt_time UInt64 , crt_user Nullable(String) , crt_name Nullable(String) , crt_host Nullable(String) , upd_time Nullable(UInt64) , upd_user Nullable(String) , upd_name Nullable(String) , upd_host Nullable(String) ) ENGINE MergeTree() primary key non_motor_vehicle_id partition BY toYYYYMMDD(FROM_UNIXTIME(crt_time)) ORDER BY (non_motor_vehicle_id,crt_time) SETTINGS index_granularity=8192;

CREATE TABLE v_non_motor_vehicle_all on cluster perftest_2shards_1replicas AS v_non_motor_vehicle ENGINE = Distributed(perftest_2shards_1replicas, default, v_non_motor_vehicle, rand());