Как автоматизировать с Jenkins сборку и раскатку артефактов модели метаданных для таблиц в хранилище29.10.2018 18:02

alter table scheme.GP_000001_TABLE rename to Z_GP_000001_TABLE_20180807;
create table scheme.GP_000001_TABLE
(

`charge_id`   DECIMAL (38,0),
`svc_id`   DECIMAL (38,0),
`vnd_id`   DECIMAL (38,0),
`price`   DECIMAL (38,5),
`quantity`   DECIMAL (38,5),
`base`   DECIMAL (38,5),
`tax`   DECIMAL (38,5),
`total`   DECIMAL (38,5),
`tax_rate`   DECIMAL (38,5),
`tax_in`   STRING,
`charge_kind`   STRING,
`privilege_id`   DECIMAL (38,0),
`charge_ref_id`   DECIMAL (38,0),
`ebid`   DECIMAL (38,0),
`invoice_id`   DECIMAL (38,0),
`zero_state_id`   DECIMAL (38,0),
`user_id`   DECIMAL (38,0),
`bid`   DECIMAL (38,0),
`quantity_real`   DECIMAL (38,5),
`load_dttm`   TIMESTAMP,
`src_id`   SMALLINT,
`package_id`   BIGINT,
`wf_run_id`   BIGINT,
`md5`   STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 'путь для формирования в hdfs'
TBLPROPERTIES ( 'auto.purge'='true', 'transient_lastDdlTime'='1489060201');
insert into scheme.GP_000001_TABLE (   `charge_id`,
   `svc_id`,
   `vnd_id`,
   `price`,
   `quantity`,
   `base`,
   `tax`,
   `total`,
   `tax_rate`,
   `tax_in`,
   `charge_kind`,
   `privilege_id`,
   `charge_ref_id`,
   `ebid`,
   `invoice_id`,
   `zero_state_id`,
   `user_id`,
   `bid`,
   `quantity_real`,
  `load_dttm`,
  `src_id`,
  `package_id`,
  `wf_run_id`,
  `md5`)

select     `charge_id`,
   `svc_id`,
   `vnd_id`,
   `price`,
   `quantity`,
   `base`,
   `tax`,
   `total`,
   `tax_rate`,
   `tax_in`,
   `charge_kind`,
   `privilege_id`,
   `charge_ref_id`,
   `ebid`,
   `invoice_id`,
   `zero_state_id`,
   `user_id`,
   `bid`,
   `quantity_real`,
load_dttm,
src_id,
package_id,
wf_run_id,
md5 from scheme.Z_GP_000001_TABLE_20180807;

alter table scheme.GP_000001_TABLE rename to Z_GP_000001_TABLE_20180807;
create table scheme.GP_000001_TABLE
(

`charge_id`   DECIMAL (38,0),
`svc_id`   DECIMAL (38,0),
`vnd_id`   DECIMAL (38,0),
`price`   DECIMAL (38,5),
`quantity`   DECIMAL (38,5),
`base`   DECIMAL (38,5),
`tax`   DECIMAL (38,5),
`total`   DECIMAL (38,5),
`tax_rate`   DECIMAL (38,5),
`tax_in`   STRING,
`charge_kind`   STRING,
`privilege_id`   DECIMAL (38,0),
`charge_ref_id`   DECIMAL (38,0),
`ebid`   DECIMAL (38,0),
`invoice_id`   DECIMAL (38,0),
`zero_state_id`   DECIMAL (38,0),
`user_id`   DECIMAL (38,0),
`bid`   DECIMAL (38,0),
`quantity_real`   DECIMAL (38,5),
`load_dttm`   TIMESTAMP,
`src_id`   SMALLINT,
`package_id`   BIGINT,
`wf_run_id`   BIGINT,
`md5`   STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 'путь для формирования в hdfs'
TBLPROPERTIES ( 'auto.purge'='true', 'transient_lastDdlTime'='1489060201');
insert into scheme.GP_000001_CPA_CHARGE (   `charge_id`,
   `svc_id`,
   `vnd_id`,
   `price`,
   `quantity`,
   `base`,
   `tax`,
   `total`,
   `tax_rate`,
   `tax_in`,
   `charge_kind`,
   `privilege_id`,
   `charge_ref_id`,
   `ebid`,
   `invoice_id`,
   `zero_state_id`,
   `user_id`,
   `bid`,
   `quantity_real`,
  `load_dttm`,
  `src_id`,
  `package_id`,
  `wf_run_id`,
  `md5`)

select     `charge_id`,
   `svc_id`,
   `vnd_id`,
   `price`,
   `quantity`,
   `base`,
   `tax`,
   `total`,
   `tax_rate`,
   `tax_in`,
   `charge_kind`,
   `privilege_id`,
   `charge_ref_id`,
   `ebid`,
   `invoice_id`,
   `zero_state_id`,
   `user_id`,
   `bid`,
   `quantity_real`,
load_dttm,
src_id,
package_id,
wf_run_id,
md5 from scheme.Z_GP_000001_TABLE_20180807;