将oracle表格转成hive建表使用textfile格式

将oracle表格转成hive建表使用textfile格式

 oracle的数据字段如下所示

 

要知道oracle的数据类型和hive的数据类型不同，因此在转换时需要格外注意。

注意：oracle的vechar类型和date类型转换成hive都是string类型

CREATE EXTERNAL TABLE `STGB_COMM.PROD_INST_SUB`(

  `prod_inst_id` bigint COMMENT '',

  `prod_id` int COMMENT '',

  `acc_prod_inst_id` bigint COMMENT '',

  `prod_use_type` string COMMENT '',

  `acc_num` string COMMENT '',

  `account` string COMMENT '',

  `payment_mode_cd` string COMMENT '',

  `address_desc` string COMMENT '',

  `owner_cust_id` bigint COMMENT '',

  `prod_inst_pwd` string COMMENT '',

  `exch_id` int COMMENT '',

  `address_id` bigint COMMENT '',

  `region_id` bigint COMMENT '',

  `lan_id` bigint COMMENT '',

  `act_date` string COMMENT '',

  `begin_rent_date` string COMMENT '',

  `stop_rent_date` string COMMENT '',

  `status_cd` string COMMENT '',

  `create_org_id` bigint COMMENT '',

  `create_staff` bigint COMMENT '',

  `update_staff` bigint COMMENT '',

  `create_date` string COMMENT '',

  `status_date` string COMMENT '',

  `update_date` string COMMENT '',

  `first_finish_date` string COMMENT '',

  `busi_mod_date` string COMMENT '',

  `use_cust_id` bigint COMMENT '',

  `last_order_item_id` bigint COMMENT '',

  `remark` string COMMENT '',

  `point_owner_id` bigint COMMENT '',

  `ext_prod_inst_id` string COMMENT '',

  `route_id` bigint COMMENT '')

ROW FORMAT SERDE

  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES (

  'field.delim'='',

  'serialization.format'='')

STORED AS INPUTFORMAT

  'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION

  'hdfs://hacluster/warehouse/tablespace/external/hive/stgb_comm.db/prod_inst_sub'

TBLPROPERTIES (

  'bucketing_version'='2',  'transient_lastDdlTime'='1637661830')