引言

        在数据仓库建设的核心环节中，跨数据库表结构迁移是绕不开的关键步骤。尤其是在以 MySQL 作为业务数据库、Hive 作为数仓核心的技术体系中，手动将 MySQL 表结构映射并转化为 Hive 建表语句，长期以来都是数据开发人员面临的 “痛点” 场景。

        当业务库中表数量寥寥无几时，手动编写建表语句或许尚可接受；但随着业务规模扩张，数仓中往往需要承接上百张 MySQL 业务表，此时手动操作的弊端便会无限放大：不仅需要耗费大量重复劳动，浪费开发时间，更易因人工疏忽出现字段类型遗漏、注释错误、语法偏差等问题，为后续数据同步、分析建模埋下隐患。

        本文将聚焦Python 技术栈，详细拆解从 MySQL 元数据提取到 Hive 建表语句自动生成的全流程。作为系列开篇，我们会先理清技术选型逻辑，再深入解析 MySQL 与 Hive 表结构的核心差异，为后续实现自动化建表筑牢理论基础。

技术背景

MySQL 与 Hive 表结构的核心差异

MySQL 作为关系型数据库，主打事务处理与实时读写；Hive 则是基于 Hadoop 的分布式数据仓库，侧重海量数据的离线分析与存储优化。二者在表结构设计上的核心差异，是实现自动化建表必须攻克的难点，主要体现在以下两个维度：

1. 数据类型映射差异

MySQL 与 Hive 的数据类型体系并非完全一一对应，直接照搬会导致建表语句语法错误或数据存储异常。以下是高频核心数据类型的映射规则：

MySQL 数据类型	Hive 数据类型	映射说明
INT	BIGINT	Hive 中 BIGINT 取值范围更大，适配海量数据存储，避免数值溢出
TINYINT/SMALLINT	INT	兼容 MySQL 小整型，满足常规业务计数需求
VARCHAR/CHAR	STRING	Hive 无固定长度字符串类型，STRING 可兼容任意长度文本，简化映射逻辑
DATETIME/TIMESTAMP	TIMESTAMP	二者时间格式逻辑一致，Hive TIMESTAMP 支持秒级精度，适配业务时间字段
DECIMAL(p,s)	DECIMAL(p,s)	精准映射高精度数值类型，保障财务、统计类数据的精度
FLOAT/DOUBLE	DOUBLE	兼容浮点型数据，满足非精确数值的存储需求
DATE	DATE	基础日期类型直接映射，无需额外转换

2. 表属性与存储格式差异

MySQL 作为关系型数据库，表结构设计围绕事务特性展开，而 Hive 为适配分布式存储与分析，新增了大量专属特性，核心差异如下：

• 存储格式：MySQL 以 InnoDB 为默认存储引擎，依赖 B+ 树索引优化查询；Hive 则支持 ORC、Parquet 等列存格式，以及 TextFile、RCFile 等行存格式，其中 ORC/Parquet 凭借高压缩比、高效查询性能，成为数仓建设的首选格式；
• 分区表：Hive 核心优化手段之一，通过按指定字段（如日期、地区）分区，可大幅减少查询时的扫描数据量，提升分析效率；MySQL 虽支持分区，但语法与应用场景与 Hive 差异较大；
• 表属性：Hive 支持通过 TBLPROPERTIES 配置存储策略（如压缩格式、文件保留时间）、字段注释、表注释等，而 MySQL 的表属性配置相对简单，无类似分布式存储相关配置；
• 约束特性：MySQL 支持主键（PRIMARY KEY）、外键（FOREIGN KEY）等强约束，保障数据完整性；Hive 仅支持非空约束（且需手动配置），主键与外键仅作注释用，不具备强制校验功能。

信息获取途径：MySQL 的 information_schema 数据库

要实现自动化提取表结构，首先得找到存储 MySQL 所有元数据的 “源头”。而 information_schema 正是 MySQL 内置的 “元数据数据库”，它以视图形式存储了数据库、表、字段、索引、权限等所有对象的详细信息，无需额外安装配置，直接查询即可获取所需数据。

其中，与表结构提取最相关的核心视图是 COLUMNS，它包含了数据库中所有字段的元数据，关键字段及含义如下：

• TABLE_SCHEMA：所属数据库名称（即 MySQL 业务库名）；
• TABLE_NAME：表名称；
• COLUMN_NAME：字段名称；
• DATA_TYPE：字段数据类型（MySQL 原生类型）；
• COLUMN_COMMENT：字段注释；
• IS_NULLABLE：是否允许为空（YES/NO）；
• COLUMN_DEFAULT：字段默认值；
• ORDINAL_POSITION：字段在表中的顺序位置。

通过查询该视图，我们可以精准获取单表或全库的字段元数据，为后续类型映射与建表语句拼接提供核心数据支撑。

实现步骤

生成 hive 建表语句

1.在hive中创建一个数据库:

create database finance;

2.在mysql数据库，查询jrxd下的所有表名，复制到/root/tables.txt文件中

select table_name from information_schema.`TABLES` where table_schema = 'jrxd'；

或者使用简单的  show tables；

3.编写python脚本

linux 自带 python2：

第一步：安装pip2服务
wget https://bootstrap.pypa.io/pip/2.7/get-pip.py
python get-pip.py
第二步:安装pymysql
pip2 install pymysql

（可直接粘取，修改为自己的数据库）

# -*- coding: utf-8 -*-

# 给定一个数据库的名字和表的名字，自动生成hive的建表语句
import sys
import pymysql

def getDBData(dbName, tableName):
    # 查询mysql的元数据，根据数据库的名字和表的名字查询改表对应的字段和类型
    # 连接数据库（指定charset避免中文乱码）
    conn = pymysql.connect(
        host='hadoop11',
        user='root',
        password='123456',
        database='information_schema',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor  # 返回字典格式，更易读
    )
    cursor = conn.cursor()
    sql = """
                SELECT column_name, data_type
                FROM information_schema.`COLUMNS`
                WHERE TABLE_SCHEMA = %s AND table_name = %s
                ORDER BY ordinal_position  # 按字段顺序排序
            """
    # 执行sql语句
    cursor.execute(sql, (dbName, tableName))
    result=cursor.fetchall()
    # 统一将字典的key转为小写，兼容大小写场景
    result_lower = []
    for item in result:
        lower_item = {k.lower(): v for k, v in item.items()}
        result_lower.append(lower_item)

    cursor.close()
    conn.close()

    return result_lower

# 完善MySQL到Hive的类型映射（覆盖更多场景，符合生产规范）
MYSQL_TO_HIVE_TYPE_MAPPING = {
    # 整数类型
    'tinyint': 'TINYINT',
    'smallint': 'SMALLINT',
    'int': 'INT',
    'integer': 'INT',
    'bigint': 'BIGINT',
    'bit': 'BOOLEAN',
    # 浮点/定点类型
    'float': 'FLOAT',
    'double': 'DOUBLE',
    'decimal': 'DECIMAL',
    'numeric': 'DECIMAL',
    # 字符串类型
    'varchar': 'STRING',
    'char': 'STRING',
    'text': 'STRING',
    'tinytext': 'STRING',
    'mediumtext': 'STRING',
    'longtext': 'STRING',
    'enum': 'STRING',
    'set': 'STRING',
    # 日期时间类型
    'date': 'DATE',
    'datetime': 'STRING',
    'timestamp': 'STRING',
    'time': 'STRING',
    'year': 'INT',
    # 二进制类型
    'binary': 'BINARY',
    'varbinary': 'BINARY',
    'blob': 'BINARY',
    'tinyblob': 'BINARY',
    'mediumblob': 'BINARY',
    'longblob': 'BINARY',
    # 其他类型
    'boolean': 'BOOLEAN',
    'bool': 'BOOLEAN'
}

def generateHiveCreateSql(dbName, tableName,field_info):
    hive_columns = []
    for tuple in field_info:
        column_name = tuple['column_name']
        data_type = tuple['data_type']
        # 根据mysql的字段类型获取hive对应的字段类型
        hive_type=MYSQL_TO_HIVE_TYPE_MAPPING.get(data_type, "string")
        # 修复：去掉 f-string，兼容 Python2
        hive_columns.append("%s %s" % (column_name, hive_type))

    columns_str = ','.join(hive_columns)
    # 修复：多行字符串格式化兼容 Python2
    sql = """
        CREATE  TABLE IF NOT EXISTS ods_jrxd_%s (
        %s
        )
        COMMENT '%s.%s'
        ROW FORMAT DELIMITED
            FIELDS TERMINATED BY ',';
    """ % (tableName, columns_str, dbName, tableName)
    return sql

if __name__ == '__main__':
    # 校验外部参数
    if len(sys.argv) != 3:
        print("请传入数据库的名字和表的名字")
        sys.exit(1)

    dbName = sys.argv[1]
    tableName = sys.argv[2]
    print(dbName, tableName)
    field_info=getDBData(dbName, tableName)
    create_hive_table_sql=generateHiveCreateSql(dbName, tableName, field_info)
    print(create_hive_table_sql)
    with open("./hive_create_table.sql","a") as f:
        f.write(create_hive_table_sql)

4.测试该 py 是否可以使用:

python AutoCreateHiveSql.py jrxd channel_info

5.编写shell脚本

#!/bin/bash
while read x1
do
   python AutoCreateHiveSql.py jrxd $x1
done < /root/tables.txt

6.运行脚本

./datax_autosql_finance.sh

7.将sql粘贴到hive窗口运行即可