ODS_1_to_ODS_2

将ods_1的数据刷到ods_2，这时候数据已经有了分区

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os
import  pymysql
import  pymssql
import  re
import  sys
 

hosts = "12.23.35.2"
username = "xxx"
密码 = "xxx"
database = "xxx"
env = 'pro'
schema = 'pro_ods'
source = 'nc_pro'

'''table_list = ['purchase_invoice_header','purchase_invoice_line','purchase_order_header','purchase_order_line',
'purchase_request_header','purchase_request_line','purchasein_bill_header','purchasein_bill_line',
'receive_order_header','receive_order_line','saleout_bill_header','saleout_bill_line',
'sales_invoice_header','sales_invoice_line','sales_order_header','sales_order_line',
'delivery_order_header','delivery_order_line','payment_order_header','payment_order_line']
'''
table_list = ['ic_saleout_h']
#table_list = sys.argv[1].split(",")
def get_table_info(table,schema = schema,ispartition = True):
  
    cols = []
    create_head = '''
insert overwrite table {0}.{1} partition(inc_day)
select ''' .format(schema,'ods_2_'+table )
    
    create_tail = r'''
FROM {0}.{1}
;
'''.format(schema,'ods_1_'+table)

    connection=pymssql.connect(host=hosts,
                               user=username,
                               密码=password,
                               database=database,
                               charset='utf8'
                               )
    try:
    #获取一个游标
        with connection.cursor() as cursor:#connection.cursor(cursor=pymssql.cursors.DictCursor) as cursor:#
            #sql='SHOW FULL FIELDS FROM  {0}'.format(table)
            sql='select column_name,data_type from information_schema.columns where table_name = \'{0}\''.format(table)
            cout=cursor.execute(sql) #返回记录条数
            try:
                for row in cursor:#cursor.fetchall()
                    #print(type(row))
                    #print(row[0])
                    cols.append(row[0]) 
                    if 'bigint' in row[1]:
                        colunm_t = "bigint"
                    elif 'int' in row[1] or 'tinyint' in row[1] or 'bigint' in row[1] or 'smallint' in row[1] or 'mediumint' in row[1]: 
                        colunm_t = "bigint"
                    elif 'double' in row[1] or 'float' in row[1] or 'decimal' in row[1]:
                        colunm_t = "decimal"
                    else:
                        colunm_t = "string"
                    create_head += row[0] + ',\n'
                create_head += 'substring(ts,1,10) ' 
            except Exception as e:
                print('程序异常!')  
                print(e)
    finally:
        connection.close()
    create_str = create_head[:-2] + '\n' + ')'+ create_tail
    return cols,create_str # 返回字段列表与你建表语句 
#cols,create_str = get_table_info(sys.argv[1])


for i in range(0,len(table_list)):
    print(table_list)
    cols,create_str = get_table_info(table_list[i])
    #print('===================='+table_list[i]+'======================')
    print(create_str)
    #print('\n\n\n\n')
    os.system('hive -e "' + create_str +'"')