MySQL建表模板
DROP TABLE IF EXISTS `company_info`;
CREATE TABLE `company_info`
(
id BIGINT(20) UNSIGNED NOT NULL COMMENT 'ID',
company_id BIGINT(20) UNSIGNED NOT NULL COMMENT '公司ID',
company_name VARCHAR(255) NOT NULL COMMENT '公司名称',
company_code VARCHAR(32) NOT NULL COMMENT '公司编码',
company_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '公司注册时间',
`delete_flag` CHAR(1) NOT NULL DEFAULT 'N' COMMENT '是否删除',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',,
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 COMMENT ='公司信息表';
hive建表模板1
DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(
`common` STRUCT<ar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,mid :STRING,os :STRING,uid :STRING,vc
:STRING> COMMENT '公共信息',
`page` STRUCT<during_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id
:STRING,source_type :STRING> COMMENT '页面信息',
`actions` ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',
`ts` BIGINT COMMENT '时间戳'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/ods/ods_log_inc/';
hive建表模板2
--数据装载
--2020-06-14
load data inpath '/log/topic_log/2023-06-14' into table ods_log_inc partition(dt='2023-06-14');
select * from ods_log_inc;
DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full
(
`id` STRING COMMENT '活动id',
`activity_name` STRING COMMENT '活动名称'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION 'ods/ods_activity_info_full/';
不懂的地方请自行搜索或者留言。
4.mysql更新时间表的eg
CREATE DATABASE es_db;
USE es_db;
DROP TABLE IF EXISTS es_table;
CREATE TABLE es_table (
id BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_id (id),
client_name VARCHAR(32) NOT NULL,
modification_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
insertion_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
id:这是该条记录的唯一标识符。请注意 “id” 已被定义为 PRIMARY KEY(主键)和 UNIQUE KEY(唯一键)。
client_name:此字段表示在每条记录中所存储的用户定义数据。
modification_time:在 MySQL 中插入或更改任何记录时,都会将这个所定义字段的值设置为编辑时间。
insertion_time:此字段主要用于演示目的,并非正确进行同步需满足的严格必要条件。我们用其来跟踪记录最初插入到 MySQL 中的时间。
5.mysql自动加载主键
DROP TABLE IF EXISTS `test_simple`;
CREATE TABLE `test_simple` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`1` double NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3601 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '测试表' ROW_FORMAT = Dynamic;
drop table if exists equipment1;
CREATE TABLE if not EXISTS equipment1 (
equipment1_id bigint auto_increment comment '主键',
dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
amplitude DOUBLE comment '振幅',
primary key (equipment1_id)
)ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 COMMENT ='设备通道1';
mysql设置编码方式为utf8方式建表
DROP TABLE IF EXISTS `health_management_id`;
CREATE TABLE `health_management_id` (
`fault_id` int(50) NOT NULL AUTO_INCREMENT COMMENT '故障id',
`fault_name` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '故障名称',
`model_number` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL COMMENT '型号',
`code_name` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL COMMENT '代号',
`owning_device` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL COMMENT '所属设备',
`fault_time` datetime NULL DEFAULT NULL COMMENT '故障时间',
`fault_description` text CHARACTER SET utf8 NULL COMMENT '故障描述',
`treating_processes` text CHARACTER SET utf8 NULL COMMENT '处理过程',
`effect` text CHARACTER SET utf8 NULL COMMENT '成效',
PRIMARY KEY (`fault_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
es创建索引模板
PUT my_index1
{ "settings": {
"number_of_shards": "5", //切片数
"number_of_replicas": "1" //副本数
},
"mappings": {
"properties": {
"id":{
"type": "long"
},
"name":{
"type": "text",
"analyzer": "ik_max_word", //分词器
"search_analyzer": "ik_max_word" //(2)search_analyzer表示搜索的时候用于分词搜索语句的分析器,如果不指定则与analyzer相同
},
"doubanScore":{
"type": "double"
},
"actorList":{
"properties": {
"id":{
"type":"long"
},
"name":{
"type":"keyword"
}
}
}
}
}
}
PUT health_management
{
"settings": {
"number_of_shards": "5", //切片数
"number_of_replicas": "1" //副本数
},
"mappings": {
"properties": {
"fault_name": {
"type": "text" ,
"analyzer": "ik_max_word" }, //故障名称
"model_number": { "type": "keyword" }, //型号
"code_name": { "type": "keyword" }, //代号
"owning_device": {
"type": "text" ,
"analyzer": "ik_max_word" }, //所属设备
"fault_time": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss" }, //故障时间
"fault_description": {
"type": "text" ,
"analyzer": "ik_max_word" }, //故障描述
"treating_processes": {
"type": "text" ,
"analyzer": "ik_max_word" }, //处理过程
"effect": {
"type": "text" ,
"analyzer": "ik_max_word" } //成效
}
}
}
数据类型简单说明
string:1.keyword:不支持分层,2.text:支持分词(es默认分词是:standard)
时间数据类型:date. 可以使用的格式有三种
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd
epoch_millis(毫秒值)
后面加,format:"yyyy-MM-dd"
注:一旦我们规定了格式,如果新增不符合格式,会报错:mapper_parsing_exception