Get table structure for duplicating to local database purpose

[Freizello]

Environment

• Python: 3.6.* 64-bit
• pyodbc: 4.0.26
• OS: Windows 10
• DB: Hadoop with HiveQL
• driver: DSN Cloudera Hive Driver

Question

Hi, i need to duplicate hive database structure to local mariadb/mysql database with this code to get column name and data type: for row in cursor.columns(schema='schemaname, table='tablename'): print([row.column_name, row.data_type])

and the result is : ['col1', 12] ['col2', 12] ['col3', 91] ['col4', 91] ['col5', 91] ['col6', 4] ... ['col_n', 12]

my expectation is, to get at least column name and datatype for inserting to sql query something like this: CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT, title VARCHAR(255) NOT NULL, start_date DATE, due_date DATE, status TINYINT NOT NULL, priority TINYINT NOT NULL, description TEXT, PRIMARY KEY (task_id) ) ENGINE=INNODB;

may you explain how make it works? or there are another method that i didn't know yet?

I appreciate your help. 😄

[gordthompson]

When I connect to SQL Server and do ...

print(list([row.column_name, row.data_type] for row in crsr.columns(schema='dbo', table='Contacts')))

... I get ...

[['ID', 4], ['LastName', -9], ['FirstName', -9]]

The column names are correct and the numbers are the numeric values of the constants pyodbc.SQL_INTEGER (4) and pyodbc.SQL_WVARCHAR (-9).

Is your issue with the column name values or the data_type values?

[Freizello]

Thank you for replying my issue, i really appreciate it. I temporary solved my problem with this script:

n.b : Sorry for not quoting my code with code tag, its conflict with markdown tag.

and the result is: CREATE TABLE cb_prepaid_postpaid_201906_TEST (msisdn VARCHAR(255), status VARCHAR(255), activation_date DATE, grace_date DATE, expire_date DATE, gender VARCHAR(255), brand VARCHAR(255), priceplan VARCHAR(255), provider_id VARCHAR(255), customer_type VARCHAR(255), customer_subtype VARCHAR(255), area_hlr VARCHAR(255), region_hlr VARCHAR(255), city_hlr VARCHAR(255), cgi VARCHAR(255), lac VARCHAR(255), ci VARCHAR(255), area_lacci VARCHAR(255), region_lacci VARCHAR(255), kabupaten VARCHAR(255), kecamatan VARCHAR(255), kelurahan VARCHAR(255), cluster_lacci VARCHAR(255), branch VARCHAR(255), subbranch VARCHAR(255), last_revenue DATE, dominan_revenue VARCHAR(255), tot_bill_amount DECIMAL(38, 4), vol_broadband FLOAT(4, 0), vol_broadband_package FLOAT(4, 0), vol_broadband_payu FLOAT(4, 0), hvc_normal_ms VARCHAR(255), hvc_normal_ms_tier VARCHAR(255), cust_segment_type VARCHAR(255), cust_segment_subtype VARCHAR(255), last_recharge DATE, redeemer_new_ytd_flag VARCHAR(255), redeemer_ytd_flag VARCHAR(255), redeemer_mtd_flag VARCHAR(255), segment_tier_poin VARCHAR(255), rev_fit_pp FLOAT(4, 0), rev_voice_fit DOUBLE(8, 0), rev_voice_onnet_fit DOUBLE(8, 0), rev_voice_offnet_fit DOUBLE(8, 0), rev_sms_fit DOUBLE(8, 0), rev_data_fit DOUBLE(8, 0), rev_data_flash_fit DOUBLE(8, 0), rev_data_bb_fit DOUBLE(8, 0), rev_ir_fit DOUBLE(8, 0), rev_ir_voice_fit DOUBLE(8, 0), rev_ir_sms_fit DOUBLE(8, 0), rev_ir_data_fit DOUBLE(8, 0), rev_dig_music_fit DOUBLE(8, 0), rev_dig_video_fit DOUBLE(8, 0), rev_dig_games_fit DOUBLE(8, 0), rev_bbs_voice DECIMAL(38, 4), rev_bbs_voice_onnet DECIMAL(38, 4), rev_bbs_voice_offnet DECIMAL(38, 4), rev_bbs_voice_idd DECIMAL(38, 4), rev_bbs_voice_others DECIMAL(38, 4), rev_bbs_sms DECIMAL(38, 4), rev_bbs_sms_onnet DECIMAL(38, 4), rev_bbs_sms_offnet DECIMAL(38, 4), rev_bbs_data DECIMAL(38, 0), rev_bbs_data_payu DECIMAL(38, 4), rev_bbs_data_package DECIMAL(38, 0), rev_bbs_data_others DECIMAL(38, 4), rev_bbs_digital DECIMAL(38, 0), rev_bbs_digital_dls DECIMAL(38, 4), rev_bbs_digital_bank DECIMAL(38, 4), rev_bbs_digital_m2m DECIMAL(38, 1), rev_bbs_ir DECIMAL(38, 4), rev_bbs_others DECIMAL(38, 4), monthly_fee DECIMAL(38, 4), imei VARCHAR(255), data_capable VARCHAR(255), device_prio VARCHAR(255), os_prio VARCHAR(255), network_prio VARCHAR(255), device_type VARCHAR(255), design_type VARCHAR(255), manufacture VARCHAR(255), flag_date DATE) ENGINE=INNODB;

I have concern with this code, do you have any suggestion how to do it properly with the same result.

Thank you very much.

[mkleehammer]

That looks good. I'm going to close this, but if anyone has suggestions please continue to add them. Good luck.