# generate-impala-sql

**Repository Path**: pimeng001/generate-impala-sql

## Basic Information

- **Project Name**: generate-impala-sql
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-11-14
- **Last Updated**: 2023-12-20

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

使用步骤
1. 安装本地pyhton3环境
2. 安装可以读取xlsx的xlrd版本, pip install xlrd==1.2.0
3. 修改impala.py, 在文件最前面将拼接的系统名中替换为自己的系统名
4. 在对应数据库执行查询ddl标准数据的sql, 然后把结果ctrl+a复制粘贴到Excel第一个sheet里
   1. mysql示例
   ```
      SELECT a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_COMMENT
      FROM INFORMATION_SCHEMA.columns a 
      WHERE 
      TABLE_SCHEMA = DATABASE() and
      TABLE_NAME in (
      'hr_hurt_message', 'hr_should_income'
      ) order by a.TABLE_NAME,a.ORDINAL_POSITION asc
   ```
   2. oracle示例
   ```
      SELECT c.table_name,c.column_name, c.data_type,com.COMMENTS  FROM all_tab_columns c
         LEFT JOIN
            all_col_comments com
         ON
            c.owner = com.owner
            AND c.table_name = com.table_name
            AND c.column_name = com.column_name
         WHERE c.table_name IN (
         'BD_CUSTOMER','BD_CUSTCLASS'
         ) 
         ORDER BY c.table_name, c.COLUMN_ID 
         ;


   ```
   3. PostgreSQL示例
   ```
      select
         a.TABLE_NAME,
         a.COLUMN_NAME,
         a.DATA_TYPE,
         d.description as COLUMN_COMMENT
      from
         information_schema.columns a
      left join pg_description d on
         a.table_name::regclass = d.objoid
         and a.ordinal_position = d.objsubid
      where
         a.TABLE_SCHEMA = current_schema()
         and
      a.TABLE_NAME in ('zj_product_sacn_record_bottle_temporary', 'zj_product_sacn_record_temporary')
      order by
         a.TABLE_NAME,
         a.ORDINAL_POSITION asc
   ```
5. 执行impala.py
6. 对应的ods和dwd建表sql位于对应的txt里
7. 复制出来在dbeaver中通过执行sql脚本全部执行即可