mysql快速转hive

要将MySQL数据库中的表快速转换为Hive表，您可以执行以下步骤：

1. 首先，打开SQL查询工具，并执行以下转换查询语句：

   SET SESSION group_concat_max_len = 102400;
   SELECT a.TABLE_NAME, b.TABLE_COMMENT, concat('DROP TABLE IF EXISTS ', a.TABLE_NAME, ';CREATE TABLE IF NOT EXISTS ', a.TABLE_NAME, ' (', group_concat(concat(a.COLUMN_NAME, ' ', c.data_type2, " COMMENT '", COLUMN_COMMENT, "'") order by a.TABLE_NAME, a.ORDINAL_POSITION), ") COMMENT '", b.TABLE_COMMENT, "' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' STORED AS orcfile;") AS col_name
   FROM (
      SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_COMMENT
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = '你的库名'
   ) AS a
   LEFT JOIN information_schema.TABLES AS b ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA #选择源为mysql，目标为hive
   LEFT JOIN (
      SELECT * FROM dim_ddl_convert WHERE source = 'mysql' AND target = 'hive'
   ) AS c ON a.DATA_TYPE = c.data_type1
   WHERE b.TABLE_TYPE = 'BASE TABLE' AND a.TABLE_NAME NOT LIKE 'ods_%'
   GROUP BY a.TABLE_NAME, b.TABLE_COMMENT;

2. 如果您想将Hive表定义为外部表，可以执行以下转换查询语句：

   SET SESSION group_concat_max_len = 102400;
   SELECT a.TABLE_NAME, b.TABLE_COMMENT, concat('DROP TABLE IF EXISTS ', a.TABLE_NAME, ';CREATE EXTERNAL TABLE IF NOT EXISTS ', a.TABLE_NAME, ' (', group_concat(concat(a.COLUMN_NAME, ' ', c.data_type2, " COMMENT '", COLUMN_COMMENT, "'") order by a.TABLE_NAME, a.ORDINAL_POSITION), ") COMMENT '", b.TABLE_COMMENT, "' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' STORED AS orc;") AS col_name
   FROM (
      SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_COMMENT
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = '你的库名'
   ) AS a
   LEFT JOIN information_schema.TABLES AS b ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA #选择源为mysql，目标为hive
   LEFT JOIN (
      SELECT * FROM dim_ddl_convert WHERE source = 'mysql' AND target = 'hive'
   ) AS c ON a.DATA_TYPE = c.data_type1
   WHERE b.TABLE_TYPE = 'BASE TABLE' AND a.TABLE_NAME NOT LIKE 'ods_%'
   GROUP BY a.TABLE_NAME, b.TABLE_COMMENT;

3. 在上述查询语句中，确保将`你的库名`替换为您实际的数据库名称。

4. 执行查询语句后，将会生成一系列DDL语句，用于创建或更新Hive表的结构。

5. 根据您的需求，选择相应的DDL语句并在Hive中执行，即可将MySQL表转换为Hive表。

请注意，转换过程中可能需要根据实际情况进行调整，比如确保数据库连接和权限设置正确。同时，建议在执行DDL语句之前，对生成的语句进行仔细检查，确保转换的准确性和完整性。

引用提供了一个示例维度表`dim_ddl_convert`的建表语句，您可以使用它来存储和管理其他数据库表的结构转换信息。