2.4_1 SQL获取 MySQL 映射 Teradata 表结构

已于 2022-04-29 14:28:47 修改
阅读量4.1k 收藏 3
点赞数
分类专栏： # 2.5 TeraData 文章标签： mysql
于 2020-12-31 18:45:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43529621/article/details/111927286
版权
本文详细介绍了如何使用SQL查询MySQL的information_Schema获取表结构，并转换为Teradata兼容的格式。讨论了涉及的逻辑，包括数据类型转换、非空、主键、预警等方面的处理。同时，解释了数据字典中的COLUMNS和TABLES表字段信息，为数据库迁移和理解MySQL表结构提供了参考。
摘要由CSDN通过智能技术生成

目录

相关链接

前言

 

  前面Teradata数据中心(证券) —— 采集流程优化有提到为什么要写SQL获取 MySQL 映射Teradata表结构。

  各数据库获取表结构时，来源表（FROM TABLE）不一致，其他细节差异还有很多，例如：

  • Oracle需要读取Oralce静态数据字典表 =>Oralce系统表 静态数据字典视图，从数据字典获取表数据量拿不到当前准确值，需要先收集统计信息;
  • SQLServer不能跨库查询，每次查询需要先USE 库名，多个表位于不同库之间需要查询多次。抽数脚本SQL中需要注意FROM 库名.模式名;
  • MySQL 注释–后面需要加一个空格。拿表结构时涉及到的系统表最少，只有两个（Oracle和SQLServer都有将近10个）。

  而且需要各SQL查询结果字段顺序需要一致，才能入到【00.调研表结构转换模板】中使用，所以这部分工作量还是比较大，且需要对各类数据库有一定程度的深入研究。

  注：TD=Teradata数据库。

SQL获取 MySQL 映射 Teradata 表结构

/*
 * Author : GroupiesM
 * CurrDate : 2020/12/29
 * Version : 2.0
 * Link ：https://blog.csdn.net/qq_43529621/article/details/111927286 SQL获取 MySQL 映射 Teradata 表结构
 * Reference :	https://www.cnblogs.com/LUA123/p/11459296.html MySQL8 全部数据类型
 * 				https://www.cnblogs.com/penghq/p/10694197.html
 */
SELECT
	'MySQL' AS "DB_TYPE",
	T2.TABLE_SCHEMA AS "库名",
	T2.TABLE_NAME AS "表名",
	T2.TABLE_COMMENT AS "表中文名",
	T1.ORDINAL_POSITION AS "序号",
	T1.COLUMN_NAME AS "字段名",
	T1.COLUMN_COMMENT AS "字段中文",
	CASE 
		-- 字符类型
		WHEN T1.DATA_TYPE IN ('TINYTEXT','TEXT','MEDIUMTEXT','LONGTEXT')
			THEN 'VARCHAR(1)'
		WHEN T1.DATA_TYPE IN ('VARCHAR')
			THEN CONCAT('VARCHAR(',T1.CHARACTER_MAXIMUM_LENGTH,')')	
		WHEN T1.DATA_TYPE IN ('CHAR')
			THEN CONCAT('CHAR(',T1.CHARACTER_MAXIMUM_LENGTH,')')
		-- 布尔类型
		WHEN T1.DATA_TYPE IN ('BOOL','BOOLEAN')	
			THEN '布尔类型,待测试'
		-- 数字类型    
		WHEN T1.DATA_TYPE IN ('TINYINT','SMALLINT','MEDIUMINT','INT','INTEGER','BIGINT','DECIMAL','NUMERIC') AND NUMERIC_PRECISION > 38
			THEN '???'
		WHEN T1.DATA_TYPE IN ('TINYINT','SMALLINT','MEDIUMINT','INT','INTEGER','BIGINT','DECIMAL','NUMERIC') 
			THEN CONCAT('DECIMAL(',T1.NUMERIC_PRECISION,',',T1.NUMERIC_SCALE,')')			
		WHEN T1.DATA_TYPE IN ('FLOAT','DOUBLE')	AND NUMERIC_SCALE IS NULL 
			THEN '???'
		WHEN T1.DATA_TYPE IN ('FLOAT','DOUBLE') AND NUMERIC_PRECISION IS NOT NULL AND NUMERIC_SCALE IS NOT NULL
			THEN CONCAT('DECIMAL(',T1.NUMERIC_PRECISION,',',T1.NUMERIC_SCALE,')')
		WHEN T1.DATA_TYPE IN ('BIT')
			THEN 'INTEGER'
		-- 日期类型
		WHEN T1.DATA_TYPE IN ('DATE','TIMESTAMP') -- 'YYYY-MM-DD'
			THEN 'TIMESTAMP(6)'
		WHEN T1.DATA_TYPE IN ('DATETIME') -- 'YYYY-MM-DD hh:mm：ss'
			THEN 'TIMESTAMP(0)'
		WHEN T1.DATA_TYPE IN ('TIME') -- 'hh:mm:ss'
			THEN 'VARCHAR(8)'
		WHEN T1.DATA_TYPE IN ('YEAR') -- 'YYYY'
			THEN 'VARCHAR(4)'	
		-- 其他类型
			-- 二进制 
			WHEN T1.DATA_TYPE IN ('BINARY','VARBINARY')
				THEN 'VARCHAR(1)'
			-- BLOB
			WHEN T1.DATA_TYPE IN ('TINYBLOB','BLOB','MEDIUMBLOB','LONGBLOB')
				THEN 'VARCHAR(1)'
		ELSE
			NULL
	END AS TD_COMBINE,
	T1.COLUMN_TYPE AS SOURCE_COMBINE,
	CASE 
		WHEN T1.IS_NULLABLE = 'YES' 
			THEN 'N'
		WHEN T1.IS_NULLABLE = 'NO' 
			THEN 'Y'	
		ELSE 
			NULL
	END AS "非空",
	CASE
		WHEN T1.COLUMN_KEY='PRI' 
			THEN 'Y'
		ELSE 
			NULL
	END AS "主键", 
	CASE 
		WHEN T1.CHARACTER_MAXIMUM_LENGTH >64000
			THEN 'Y'
		WHEN T1.DATA_TYPE IN ('TINYTEXT','TEXT','MEDIUMTEXT','LONGTEXT','BINARY','VARBINARY','TINYBLOB','BLOB','MEDIUMBLOB','LONGBLOB')  
			THEN 'Y'
		ELSE NULL
	END AS "置空",
	CASE 
		WHEN T1.DATA_TYPE IN ('TINYTEXT','TEXT','MEDIUMTEXT','LONGTEXT','BINARY','VARBINARY','TINYBLOB','BLOB','MEDIUMBLOB','LONGBLOB') 
			THEN '不采集,置空'
		WHEN T1.CHARACTER_MAXIMUM_LENGTH >2000 
			THEN '超长字段,请人工审核,是否采集'
		WHEN T1.DATA_TYPE IN ('DECIMAL','NUMERIC') AND T1.NUMERIC_PRECISION > 38
			THEN '数值类型超长'
		ELSE NULL
	END AS "预警",
	CASE 
		WHEN T2.TABLE_TYPE = 'base table' 
			THEN "表"
		WHEN T2.TABLE_TYPE = 'view' 
			THEN "视图,表结构仅供参考"
		WHEN T2.TABLE_TYPE = 'system view' 
			THEN "MySQL系统表"
		ELSE NULL
	END AS "表/视图",
	T2.TABLE_ROWS AS "数据量",
	T1.DATA_TYPE AS "DATA_TYPE",
	T1.CHARACTER_OCTET_LENGTH AS "字节数",
	T1.CHARACTER_MAXIMUM_LENGTH AS  "长度",
	T1.NUMERIC_PRECISION AS "精度",
	T1.NUMERIC_SCALE AS "标度"
FROM 
	information_Schema.`COLUMNS` T1
LEFT JOIN 
	information_schema.TABLES T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T1.TABLE_SCHEMA = T2.TABLE_SCHEMA
WHERE 
	-- 这里填写条件【库名】
	T2.TABLE_SCHEMA IN ('AAA','BBB')
	-- 这里填写条件【表名】
	AND T2.TABLE_NAME IN (
	N'account_bsonrisk',
	N'account_subaccount',
	N'product_accounts',
	N'product_productworkflow',
	N'workflow_nodes',
	N'auth_group',
	N'product_flowusers',
	N'auth_user_groups',
	N'user_mac_address',
	N'product_product')
ORDER BY
	T2.TABLE_SCHEMA,	-- 库名
	T2.TABLE_NAME,		-- 表名
	T1.ORDINAL_POSITION	-- 字段序号

一、数据字典

  • 在程序中，若想要动态地得到某一个表的具体信息，就使用到了MySQL 中的 INFORMATION_SCHEMA 信息数据库，而它又包含很多表，见下：
INFORMATION_SCHEMA SCHEMATA表
INFORMATION_SCHEMA TABLES表	 -- T2
INFORMATION_SCHEMA COLUMNS表 -- T1
INFORMATION_SCHEMA STATISTICS表
INFORMATION_SCHEMA USER_PRIVILEGES表
INFORMATION_SCHEMA SCHEMA_PRIVILEGES表
INFORMATION_SCHEMA TABLE_PRIVILEGES表
INFORMATION_SCHEMA COLUMN_PRIVILEGES表
INFORMATION_SCHEMA CHARACTER_SETS表
INFORMATION_SCHEMA COLLATIONS表
INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY表
INFORMATION_SCHEMA TABLE_CONSTRAINTS表
INFORMATION_SCHEMA KEY_COLUMN_USAGE表
INFORMATION_SCHEMA ROUTINES表
INFORMATION_SCHEMA VIEWS表
INFORMATION_SCHEMA TRIGGERS表
  • 此次数据字典根据测试的版本整理，字段可能受版本变化影响而有变化
SELECT version(); 
> 5.5.31-log

  这里获取表结构的SQL，甚至对系统表也是可以查看的
在这里插入图片描述
  下面整理涉及到T1 T2两个表的表结构

1.1 information_Schema.COLUMNS 字段信息字典 T1

序号
Serial		字段
ColumnName		字段类型
DataType		描述
Description		备注
Note
1TABLE_CATALOGvarchar(512)MySQL官方文档中说，这个字段值永远是def(应该是default缩写)，但没写这个字段是干嘛用的。网上有把这个叫表限定符的，有叫登记目录的。作用疑似是和其他种类的数据库做区分。The name of the catalog to which the table containing the column belongs. This value is always def.
2TABLE_SCHEMAvarchar(64)所属库名。表模式(MySQL中模式表示库)。
例如：information_Schema		T2.TABLE_SCHEMA AS “库名”,
3TABLE_NAMEvarchar(64)表名。
例如：COLUMNS		T2.TABLE_NAME AS “表名”,
4COLUMN_NAMEvarchar(64)列名。T1.COLUMN_NAME AS “字段名”
5ORDINAL_POSITIONbigint(21) unsigned列序号，可以理解为列的自增主键，从1开始。有兴趣的可以测试一下删除字段是否会重新排序T1.ORDINAL_POSITION AS “序号”
6COLUMN_DEFAULTlongtext列的默认值
7IS_NULLABLEvarchar(3)是否允许为空，字段约束的一种，允许则值为YES 不允许值为NO。 CASE
 WHEN T1.IS_NULLABLE = ‘YES’
  THEN ‘N’
 WHEN T1.IS_NULLABLE = ‘NO’
  THEN ‘Y’
 ELSE
  NULL
END AS “非空”
这里取的逻辑是非空，所以和IS_NULLABLE是相反的
8DATA_TYPEvarchar(64)数据类型 。
例如：varchar
SQL中用到了逻辑判断，如右侧所示，DATA_TYPE=字符类型时，拼接为 VARCHAR(长度)		CASE
  WHEN
    T1.DATA_TYPE IN (‘VARCHAR’)
  THEN
    CONCAT(‘VARCHAR(
’,T1.CHARACTER_MAXIMUM_LENGTH,‘)’)
9CHARACTER_MAXIMUM_LENGTHbigint(21) unsigned长度。字段的最大字符数。
假如字段设置为varchar(50)，那么这一列记录的值就是50。
该列只适用于二进制数据，字符，文本，图像数据。其他类型数据比如int，float，datetime等，在该列显示为NULL。
数字类型在该列显示为NULL。
SQL中用到了逻辑判断，如右侧所示，VARCHAR类型时拼接为=> VARCHAR(此字段)		CASE
  WHEN
    T1.DATA_TYPE IN (‘VARCHAR’)
  THEN
    CONCAT(‘VARCHAR(
’,T1.CHARACTER_MAXIMUM_LENGTH,‘)’)
10CHARACTER_OCTET_LENGTHbigint(21) unsigned占用字节数（磁盘空间）。字段的最大字节数。
和最大字符数一样，只适用于二进制数据，字符，文本，图像数据，其他类型显示为NULL。
和最大字符数的数值有比例关系，和字符集有关。比如UTF8类型的表，最大字节数就是最大字符数的3倍。
例如：UTF8编码下 varchar(30) 类型 字符长度为30 ，占用字节数为90。
11NUMERIC_PRECISIONbigint(21) unsigned精度。适用于各种数字类型比如int，float的。如果字段设置为int(10)，那么在该列保存的数值是9，少一位，还没有研究原因。
如果字段设置为float(10,3)，那么在该列保存的数值是10。
非数字类型显示为在该列NULL。
double(M,D)类型省略M,D时：精度为22。此列为NULL。		WHEN
 T1.DATA_TYPE IN (‘FLOAT’,‘DOUBLE’) AND NUMERIC_SCALE IS NULL
THEN ‘???’
WHEN
 T1.DATA_TYPE IN (‘FLOAT’,‘DOUBLE’) AND NUMERIC_PRECISION IS NOT NULL AND NUMERIC_SCALE IS NOT NULL
THEN
 CONCAT(‘DECIMAL(
’,T1.NUMERIC_PRECISION,‘,’,
T1.NUMERIC_SCALE,‘)’)
翻译：如果对按类型的标度为0时，不明确转换TD数据类型，取???，需要人工确认；如果是浮点类型且有精度，标度则拼接DECIMAL(精度,标度)
12NUMERIC_SCALEbigint(21) unsigned标度1。小数位数。
和数字精度一样，适用于各种数字类型比如int，float之类。
如果字段设置为int(10)，那么在该列保存的数值是0，代表没有小数。
如果字段设置为float(10,3)，那么在该列保存的数值是3。
非数字类型显示为在该列NULL。		同上
13CHARACTER_SET_NAMEvarchar(32)字段字符集名称。例如：utf8。
14COLLATION_NAMEvarchar(32)字符集排序规则。
例如：
utf8_general_ci，是不区分大小写的UTF8排序规则。
utf8_general_cs，是区分大小写的UTF8排序规则。
utf8_bin，是区分大小写UTF8的（二进制）排序规则。
15COLUMN_TYPElongtext 字段类型。字段的数据类型+具体长度/精度/标度 。
如 varchar(32)		T1.DATA_TYPE AS “DATA_TYPE”
16COLUMN_KEYvarchar(3)字段约束。可能的值有
 PRI（主键约束）
 UNI（唯一约束）
 MUL（可重复）
 空值（无索引）		 CASE
 WHEN T1.COLUMN_KEY=‘PRI’
  THEN ‘Y’
 ELSE
  NULL
END AS “主键”,
17EXTRAvarchar(27)其他信息。
例如：主键的auto_increment（自增）。
18PRIVILEGESvarchar(80)权限。多个权限用逗号隔开。
例如：select,insert,update,references
19COLUMN_COMMENTvarchar(1024)字段注释。T1.COLUMN_COMMENT AS “字段中文”

1.2 information_Schema.TABLES 表信息字典

序号
Serial		字段
ColumnName		字段类型
DataType		描述
Description		备注
Note
1TABLE_CATALOGvarchar(512)MySQL官方文档中说，这个字段值永远是def(应该是default缩写)，但没写这个字段是干嘛用的。网上有把这个叫表限定符的，有叫登记目录的。作用疑似是和其他种类的数据库做区分。The name of the catalog to which the table belongs. This value is always def.
2TABLE_SCHEMAvarchar(64)所属库名。表模式(MySQL中模式表示库)。
例如：information_Schema		T2.TABLE_SCHEMA AS “库名”,
3TABLE_NAMEvarchar(64)表名。
例如：COLUMNS		T2.TABLE_NAME AS “表名”,
4TABLE_TYPEvarchar(64)表类型。可能的值有
 BASE TABLE（表）
 VIEW（视图）
 SYSTEM VIEW（information_schema系统表）
information_schema.tables 没有列出临时表。		CASE
 WHEN T2.TABLE_TYPE = ‘base table’
  THEN “表”
 WHEN T2.TABLE_TYPE = ‘view’
  THEN “视图,表结构仅供参考”
  WHEN T2.TABLE_TYPE = ‘system view’
  THEN “MySQL系统表”
 ELSE NULL
END AS “表/视图”,
5ENGINEvarchar(64)使用的数据库引擎。可能的值有[ InnoDB | MyISAM | MEMORY | CSV | ARCHIVE | BLACKHOLE | MERGE | FEDERATED | EXAMPLE ]
6VERSIONbigint(21) unsigned版本。默认值为10（表示MySQL 5.7中使用的最后一个.frm文件版本）。由于MySQL 8.0中删除了.frm文件，此列已经不再使用。
7ROW_FORMATvarchar(10)行格式。可能的值有
 Fixed（固定的）
 Dynamic（动态的）
 Compressed（压缩的）
 Redundant（冗余的）
 Compact（紧凑的）		1.行格式、数据页结构
2.innodb 数据页结构&行格式
8TABLE_ROWSbigint(21) unsigned行数。
  ● 一些存储引擎，如MyISAM，精确显示数据量。
  ● 对于其他存储引擎，例如InnoDB，该值仅供参考（可能与实际值相差40%到50%）。
   ○ 在这种情况下，使用SELECT COUNT(*)来获得准确的数据量。
   ○ 如果InnoDB表是分区的，TABLE_ROWS 值也是准确的。
  ● 对于INFORMATION_SCHEMA系统表，TABLE_ROWS 为空。
9AVG_ROW_LENGTHbigint(21) unsigned平均行长度。应该是统计信息，用于生成执行计划。
10DATA_LENGTHbigint(21) unsigned数据文件长度、数据空间量。
  ● 对于 MyISAM , DATA_LENGTH 是数据文件的长度，单位为字节。
  ● 对于 InnoDB , DATA_LENGTH 是为聚集索引分配的大约空间量，以字节为单位。具体来说，它是聚集索引大小(以页为单位)乘以InnoDB 数据页2 大小。
11MAX_DATA_LENGTHbigint(21) unsigned数据文件的最大长度。
  ● 对于 MyISAM , MAX_DATA_LENGTH是数据文件的最大长度。这是给定所使用的数据指针大小，可以存储在表中的数据字节总数。
  ● 对于 InnoDB ,未使用此列。
12INDEX_LENGTHbigint(21) unsigned索引文件长度。
  ● 对于MyISAM, INDEX_LENGTH是索引文件的长度，单位为字节。
  ● 对于InnoDB, INDEX_LENGTH是分配给非聚簇索引的大约空间量，单位为字节。=非聚簇索引大小总和(以页为单位)×InnoDB数据页大小。
13DATA_FREEbigint(21) unsigned空闲空间(已分配但未使用的字节数)。对于分区表该值仅供参考。
14AUTO_INCREMENTbigint(21) unsigned自增主键的下个自增值。
15CREATE_TIMEdatetime表的创建时间。
16UPDATE_TIMEdatetime最近一次数据文件更新时间。
17CHECK_TIMEdatetime最近一次表的检查时间。
  ● 对于分区InnoDB表，CHECK_TIME 总是为 NULL。
18TABLE_COLLATIONvarchar(32)表的默认排序规则。不显式出表的默认字符集，但排序规则名称以字符集名称开头。
19CHECKSUMbigint(21) unsigned实时校验和值(如果有的话)。
20CREATE_OPTIONSvarchar(255)创建表时使用的额外选项。
21TABLE_COMMENTvarchar(2048)表注释。T2.TABLE_COMMENT AS “表中文名”,

二、SQL涉及到的逻辑介绍

2.1 DB_TYPE

  用于标记数据库类型。不同类型数据库要注意的点不一样，比如SQLServer库名要加模式名，SQLServer和MySQL抽数脚本SQL可能需要注意大小写。
  这里取值MySQL。

	'MySQL' AS "DB_TYPE",

2.2 库名

  如果是SQLServer则是数据库名.模式名。

	T2.TABLE_SCHEMA AS "库名",

2.3 表名

  取T1.TABLE_NAME或T2.TABLE_NAME都一样。
  不论上游表名大小写，入TD库建表统一大写。

	T2.TABLE_NAME AS "表名",

2.4 表中文名

  如果建表不规范查不到中文名的，需要调研人员手动填写，不填写则视作没有中文。

	T2.TABLE_COMMENT AS "表中文名",

2.5 序号

  有时从测试环境拿表结构，投产时发现和生产不一致。用序号可以方便对齐，找到缺失字段。

	T1.ORDINAL_POSITION AS "序号",

2.6 字段名

  有些字段在上游库不是关键字，但在Tearada中可能是关键字，建表时需要注意表名改为_1 ，使用关键字建表会报错失败。使用【01.采集模板】可以自动检测关键字并拼接_1。

	T1.COLUMN_NAME AS "字段名",

2.7 字段中文

    如果建表不规范查不到中文名的，需要调研人员手动填写，不填写则视作没有中文。

	T1.COLUMN_COMMENT AS "字段中文",

2.8 TD_COMBINE

  转换逻辑主要参考 MySQL8 全部数据类型

2.8.1 字符类型

2.8.1.1 BLOB和TEXT

  不采集，FASTLOAD不支持加载LOB类型。LONGTEXT类型测试过抽取，但加载失败了，不确定原因，可能也不支持。

  • 抽数脚本SQL置空=>（SELECT ‘’ AS COL_NAME FROM XXX）
  • 建表VARCHAR(1) ，如果后续需求说字段必须要采集，VARCHAR类型可以用扩字段方式来改表，不需要重新建表+数据回插。
		-- 字符类型
		WHEN T1.DATA_TYPE IN ('TINYTEXT','TEXT','MEDIUMTEXT','LONGTEXT')
			THEN 'VARCHAR(1)'

在这里插入图片描述

2.8.1.2 CHAR和VARCHAR

  VARCHAR => VARCHAR(长度)
  CHAR   => CHAR  (长度)

		WHEN T1.DATA_TYPE IN ('VARCHAR')
			THEN CONCAT('VARCHAR(',T1.CHARACTER_MAXIMUM_LENGTH,')')	
		WHEN T1.DATA_TYPE IN ('CHAR')
			THEN CONCAT('CHAR(',T1.CHARACTER_MAXIMUM_LENGTH,')')

在这里插入图片描述

2.8.2 布尔类型

  目前还没遇到布尔类型，遇到后修改SQL即可，预计映射为VARCHAR(固定值)

		-- 布尔类型
		WHEN T1.DATA_TYPE IN ('BOOL','BOOLEAN')	
			THEN '布尔类型,待测试'

在这里插入图片描述

2.8.3 数字类型

  1. 如果精度>38，Teradata中是不支持建字段的，采集MySQL测试的第一个表就出现了DECIMAL(65,2)。取???，人工调研确认。一般有两种结果

  • 假如确认业务数据该字段不会超过5位数，没有小数，TD中可以建DECIMAL(5,0)。但这样有风险，因为上游是可以存进去的。
  • 建成VARCHAR(100)，这样一定能存进去，但下游使用数据的时候比较麻烦 需要先CAST(字段 AS DECIMAL(XX,XX))

   2. 如果是整数类型，则拼接 DECIMAL(精度,标度)

   3. 如果是浮点类型，则需要先判断有无标度值

  • 如果没有标度值无法给出映射关系，需要人工调研确认
  • 如果有标度值，则拼接 DECIMAL(精度,标度)

   4. BIT类型映射为INTEGER

		-- 数字类型    
		WHEN T1.DATA_TYPE IN ('TINYINT','SMALLINT','MEDIUMINT','INT','INTEGER','BIGINT','DECIMAL','NUMERIC') AND NUMERIC_PRECISION > 38
			THEN '???'
		WHEN T1.DATA_TYPE IN ('TINYINT','SMALLINT','MEDIUMINT','INT','INTEGER','BIGINT','DECIMAL','NUMERIC') 
			THEN CONCAT('DECIMAL(',T1.NUMERIC_PRECISION,',',T1.NUMERIC_SCALE,')')			
		WHEN T1.DATA_TYPE IN ('FLOAT','DOUBLE')	AND NUMERIC_SCALE IS NULL 
			THEN '???'
		WHEN T1.DATA_TYPE IN ('FLOAT','DOUBLE') AND NUMERIC_PRECISION IS NOT NULL AND NUMERIC_SCALE IS NOT NULL
			THEN CONCAT('DECIMAL(',T1.NUMERIC_PRECISION,',',T1.NUMERIC_SCALE,')')
		WHEN T1.DATA_TYPE IN ('BIT')
			THEN 'INTEGER'

在这里插入图片描述

2.9 SOURCE_COMBINE

   源库字段类型。可以直接从Mysql系统表获取。（Oracle ，SQLServer，PG都需要用CASE WHEN手动拼接）

	T1.COLUMN_TYPE AS SOURCE_COMBINE,

2.10 非空

   用于调研后填写文档。

	CASE 
		WHEN T1.IS_NULLABLE = 'YES' 
			THEN 'N'
		WHEN T1.IS_NULLABLE = 'NO' 
			THEN 'Y'	
		ELSE 
			NULL
	END AS "非空",

2.11 主键

   TD建表主键和上游保持一致。

	CASE
		WHEN T1.COLUMN_KEY='PRI' 
			THEN 'Y'
		ELSE 
			NULL
	END AS "主键",

2.12 置空

   用于调研后填写文档。

	CASE 
		WHEN T1.CHARACTER_MAXIMUM_LENGTH >64000
			THEN 'Y'
		WHEN T1.DATA_TYPE IN ('TINYTEXT','TEXT','MEDIUMTEXT','LONGTEXT','BINARY','VARBINARY','TINYBLOB','BLOB','MEDIUMBLOB','LONGBLOB')  
			THEN 'Y'
		ELSE NULL
	END AS "置空",

2.13 预警

   对可能出现问题的点给出提示，如果采集加载报错，只关注预警字段即可。

   对不采集的类型给出提示，需要修改对应抽数脚本。

    例如：A库B表note字段为BLOB类型，则抽数脚本尾 SELECT ‘’ AS note FROM A.B

   字段超过2000的一般为备注信息，没有实际用途的可以也置空。

   TD库支持VARCHAR字段最大值为64000，大于这个值一定加载不了，置空处理。

   TD库支持DECIMAL(M,N)中M范围为 [1,38]。大于38已经超出范围，所以这种类型也需要预警提示。

	CASE 
		WHEN T1.DATA_TYPE IN ('TINYTEXT','TEXT','MEDIUMTEXT','LONGTEXT','BINARY','VARBINARY','TINYBLOB','BLOB','MEDIUMBLOB','LONGBLOB') 
			THEN '不采集,置空'
		WHEN T1.CHARACTER_MAXIMUM_LENGTH >2000 
			THEN '超长字段,请人工审核,是否采集'
		WHEN T1.DATA_TYPE IN ('DECIMAL','NUMERIC') AND T1.NUMERIC_PRECISION > 38
			THEN '数值类型超长'
		WHEN T1.CHARACTER_MAXIMUM_LENGTH >64000
			THEN '字符类型超长,直接置空'	
		ELSE NULL
	END AS "预警",

2.14 表/视图

   视图类型的表结构需要人工确认。需要下载视图的建表语句、和视图来源表的表结构。

	CASE 
		WHEN T2.TABLE_TYPE = 'base table' 
			THEN "表"
		WHEN T2.TABLE_TYPE = 'view' 
			THEN "视图,表结构仅供参考"
		WHEN T2.TABLE_TYPE = 'system view' 
			THEN "MySQL系统表"
		ELSE NULL
	END AS "表/视图",

2.15 数据量

   一定程度影响调研人员给出的采集算法–增量/全量 ，另一方面对数据量较大的表（例如大于一亿）一般要分批补历史（每次抽取几个月的数据）后，每日S层增量抽取。

	T2.TABLE_ROWS AS "数据量",

2.16 DATA_TYPE

   用于校验的Td字段类型拼接是否准确。还有???类型的字段，需要根据这里人工判断TD表结构。

	T1.DATA_TYPE AS "DATA_TYPE",

2.17 字节数

   用于校验的Td字段类型拼接是否准确。还有???类型的字段，需要根据这里人工判断TD表结构。

	T1.CHARACTER_OCTET_LENGTH AS "字节数",

2.18 长度

   用于校验的Td字段类型拼接是否准确。还有???类型的字段，需要根据这里人工判断TD表结构。

	T1.CHARACTER_MAXIMUM_LENGTH AS  "长度",

2.19 精度

   用于校验的Td字段类型拼接是否准确。还有???类型的字段，需要根据这里人工判断TD表结构。

	T1.NUMERIC_PRECISION AS "精度",

2.20 标度

   用于校验的Td字段类型拼接是否准确。还有???类型的字段，需要根据这里人工判断TD表结构。

	T1.NUMERIC_SCALE AS "标度"

20/12/28

M

  1. Scale（标度）：在（Oracle/MySQL/SQLServer）数据类型之长度、精度、标度有详细研究。
      长度：占计算机存储容量，在上图中显示的Length表示长度，单位为字节 例如：int类型占用4个字节
      精度：指数字的位数。例如：数 123.45 的精度是 5。
      标度：指小数点后的数字位数。 例如：数 123.45 的标度是 2。 ↩︎

  2. InnoDB page（数据页）：
      我们先大致了解MySQL的存储数据方式：无论是存储数据还是读取数据，我们与MySQL的交互都是基于内存的，这样能保证速度。但是MySQL毕竟是基于磁盘的，所以就必变成读取的时候从磁盘刷到内存，存储的时候从内存刷到磁盘。那就出现一个问题：一次刷新一条记录，则取几万条数据就得和磁盘交互几万次，那磁盘的速度是非常慢的，所以引入了数据页的概念，innodb的数据页默认是16kb大小，每次从磁盘读取数据最小单位是页，即即使只读一条数据，仍旧会从磁盘读取一整个数据页。
      那页与页之间是如何联系的？页里面又是如何存储的？答案是：页与页之间是通过双链表来联系的，每个页都有header来记录相关信息，hearder里包含了上下页的位置，这样就跟双链表一样通过指针联系在了一起！页里面是以记录的形式存储一行行的数据，也就是每insert into一条数据，就成为一行记录存储在数据页，记录与记录之间是以单链表的形式连接在一起的，每条记录也有所谓的header，里面包含了下一条所在位置的信息，即下一条相对位置的指针，即能以单链表的形式连接在一起。 ↩︎

GroupiesM
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
teradata数据库--常用的数据字典表
wali_wang的博客
01-08 1万+
teradata使用过程中我们多多少需要查看下 created objects的相关信息，对于查看表结构一句SHOW TABLE TNAME就可以完全胜任。假设我们有诸如一下的需求呢： 查看那些表是FALLBACK或者NO FALLBACK 统计下某库下总共有多少个view 多少个Macro 多少个Stored Procedure 查询表的约束 查询那些表是以XXXX列为PI的
teradata架构及结构
03-29
teradata架构，以及基础知识； automation的结构和说明，以及模型
sql 获取表结构
yufei1422的专栏
12-03 247
--取得所有数据库名(要先连接到Master数据库) select * from sysdatabases --取得某个数据库中的所有用户表名(要先连接到指定的数据库) select * from sysobjects where xtype='U' --取得某个表的所有字段信息(要先连接到指定的数据库) select * from syscolumns where
mysql查看整库个表详情
weixin_30933531的博客
05-16 528
information_schema.tables字...
teradata 查看 表定义_Teradata_DBC_系统表
weixin_28801391的博客
12-23 701
object_nametypequeuefallbackverssionAccLogRuleMacroNF1ARC_NonEmpty_ListMacroNF1ClearAccountingMacroNF1ClearAllDatabaseUseCountMacroNF1ClearDatabaseUseCountMacroNF1ClearPeakDiskMacroNF1ClearTVMUseCount...
python自动生成sql建表语句
03-19
我们要讨论的是如何用Python编写程序，以读取某种数据源（例如Excel文件），然后根据数据结构自动生成适用于不同数据库系统的SQL创建表的语句，如Oracle、MySQL和Teradata。这涉及到Python的数据处理、文件操作以及...
隐式格式的MATLAB代码-utl_explicit_pass_through_to_mysql_to_subset_a_table_usin
05-22
隐式格式的MATLAB代码utl_explicit_pass_through_to_mysql_to_subset_a_table_using_macro_variable_dates 显式传递到mySQL以使用宏变量日期对表进行子集设置。 关键字：sas sql join合并大数据分析宏oracle ...
matlabeof代码-utl_apply_rules_in_table_2_to_each_record_of_table_1:将表2中的规
06-16
sql join 合并大数据分析宏 oracle teradata mysql sas 社区 stackoverflow 统计人工智能 AI Python R Java Javascript WPS Matlab SPSS Scala Perl CC# Excel MS Access JSON 图形映射 NLP自然语言处理机器学习 ...
matlab导入excel代码-utl_convert_pdf_tables_to_SAS_WPS_datasets:将pdf表转换为SASW
05-21
sql join合并大数据分析宏oracle teradata mysql sas社区stackoverflow statistics人工智慧AI Python R Java Javascript WPS Matlab SPSS Scala Perl CC＃Excel MS Access JSON图形映射NLP自然语言处理机器学习...
Replicating Transactions Between MS SQL Server and Oracle Database
08-04
首先，需要理解的是，Oracle GoldenGate 支持多个不同平台之间的事务级复制，包括但不限于 Oracle Database、IBM DB2、Microsoft SQL Server、MySQL、Teradata等，这也包括通过通用ODBC驱动程序进行访问。...
SystemView教程（基础篇、提高篇）
06-06
SystemView 是一个用于现代工程与科学系统设计及仿真的动态系统分析平台。本教程从安装，图符，基本操作，进阶等循序渐进地指导，希望对大家能有帮助！
常用软件1
qq_38670470的博客
05-14 199
常用工具汇总 Teradata Administrator 14.10 TD工具，查看分层和表结构 Teradata SQL Assistant TD执行sql工具 例：select top 10 * from pcrm_view_dev.test DBeaver 开源数据库工具 Beyond Compare 3 差异比对工具，支持文件比对和文件夹比对 Filezilla 文件传输工具 PowerDesigner 数据模型工具 FireFly Client 版本管理 notepad++ 代码编写 Vis
SQL查询将结果保存为新表
最新发布
originalmb的博客
10-12 1万+
将查询的结果保存到新表中
2.5_2 Teradata数据库 -- 常用系统表之 DBC.Columns 表字段信息
GroupiesM的博客
07-03 8294
Teradata数据库 通过系统表 DBC.Columns 获取字段信息，表结构，数据类型代码和实际类型映射关系的测试研究过程
mysql数据迁移到teradata_Teradata SQL Assistant工具导入与导出
weixin_39713814的博客
02-28 1476
Teradata SQL Assistant工具导入与导出1.使用Teradata SQL Assistant工具导入与导出当你得到一个EXCEL平面文件数据的时候，你就可以利用Teradata SQLAssistant工具导入导出，当然你也可以利用BTEQ字符界面的工具来导入导出，这里暂且先不介绍BTEQ的用法。www.2cto.com1.1转换EXCEL平面文件把EXCEL文件的数据转换成...
MySQL系统表及补充（学习笔记）
weixin_44751941的博客
10-15 659
文章目录@[toc]Mysql数据库主要系统库、表说明四个系统自带库：1. information_schema① information_schema主要表② TABLES表③ COLUMNS表④ KEY_COLUMN_USAGE表⑤ TABLE_CONSTRAINTS表⑥ STATISTICS表2. mysql3. performance_schema4. sysmysql有关show的用法用户1.查看所有用户2.查看当前登陆用户3.创建新用户4.授权用户5.回收权限6.删除用户7.设置.
MySQL的view（视图）简介
m0_49790240的博客
03-27 6919
一、视图是什么？ 视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。因此，视图中的数据是依赖原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。 二、为什么要使用视图？ 为了提高SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。视图使开发者只关心使用到的某些特定数据和所负责的特定任务，只能看
teradata是MySQL吗_Teradata数据库——你需要了解的基础知识
weixin_34564735的博客
01-28 549
AMP (Access Module Processors)TD里面最基础的核心组件之一，它是存储、计算TD表中每一行数据的基本单位。你会在很多地方看到它的身影。每一个AMP有自己的一块内存、有自己的一块计算资源和自己的一块磁盘空间。AMP一条SQL在TD中的执行过程TD简单架构用户输入一条SQL，传递到PE，PE给出一个执行计划(使用explain可以看到)，将计划执行的任务通过BYNET传递到...
teradata 查看 表定义_teradata查询表结构，
weixin_42581003的博客
12-23 1161
teradata查询表结构，show table tablename;help table tablename;SELECT UPPER(TRIM(T1.COLUMNNAME)),COALESCE(T1.COLUMNTITLE,'NO TITLE'),CASE WHEN T1.COLUMNTYPE = 'BF' THEN'BYTE'WHEN T1.COLUMNTYPE = 'I1' THEN'B...
"MySQL、Teradata和PySpark代码互转表和数据转换操作详解"。
对于Teradata SQL，删除表的操作与MySQL类似，语法格式为DROP TABLE [<架构名称>.]<表名>。而对于创建表并插入查询数据的操作，则需要使用相应的Teradata SQL语句，该文档未提供详细示例。 在PySpark中，我们可以...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值