Importing HDFs data to Hive

1. Via Hive View

CREATE EXTERNAL TABLE if not exists finance.json_serde_optd_table (RetCode string, retmsg string, data array<struct < secid:string,= "" tradedate:date,= "" optid:string,= "" ticker:string,= "" secshortname:string,= "" EXCHANGECD: string,= "" presettleprice:double,= "" precloseprice:double,= "" openprice:double,= "" highestprice:double,= "" lowestprice:double,= "" closeprice:double,= "" settlprice:double,= "" turnovervol:double,= "" turnovervalue:double,= "" Openint:int= "" >>) ROW FORMAT SERDE ' org.apache.hive.hcatalog.data.JsonSerDe ' location ' hdfs://wdp.xxxxx.cn : 8020/nifi/finance1/optd/'; CREATE table if not exists Finance.tb_optdasselect b.data.secid,b.data.tradedate, B.data.optid,b.data.ticker,b.data.secshortname,b.data.exchangecd,b.data.presettleprice,b.data.precloseprice, B.data.openprice,b.data.highestprice,b.data.lowestprice,b.data.closeprice,b.data.settlprice,b.data.turnovervol , b.data.turnovervalue,b.data.openintfrom finance.json_serde_optd_table lateral VIEW explode (json_serde_optd_tAble.data) b as data; 

?

2. By Zeppelin

?

%depz.load ("/usr/hdp/2.4.2.0-258/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar");

?

Define a collection of imported hive objects case Class Hiveconfig (database:string, modelname:string, hdfspath:string, schema:string, schema_tb:st ring); var hiveconfiglist = List[hiveconfig] ();

?

Create EQUD data structure//define JSON structure val Schema_json_equd_serde = "" "RetCode string, retmsg string, Data array<struct< secid= "": = "" string,= "" tradedate= "" date,= "" ticker= "" Secshortname= "" exchangecd= "precloseprice=" "double,=" "actprecloseprice:=" "openprice=" "highestprice=" "lowestprice=" "Closeprice = "Turnovervol=" "turnovervalue=" "dealamount=" "int,=" "turnoverrate=" "accumadjfactor=" "negmarketvalue=" "            Marketvalue= "pe=" "pe1=" "pb=" "isopen=" "int=" ">>" "; var schema_equd =" "B.data.secid, B.data.ticker, B.data.secshortname, B.DATA.EXCHANGECD, B.data.tradedate, B.data.precloseprice , B.data.actprecloseprice, B.data.openprice, B.data.highestprice, B.data.lowes Tprice, B.data.closeprice, B.data.turnovervol, B.data.turnovervalue, b.data.de Alamount, B.data.turnoverrate,            B.data.accumadjfactor, B.data.negmarketvalue, B.data.marketvalue, b.data.pe, B.data.pe1, B.DATA.PB, B.data.isopen "" "; hiveconfiglist = hiveconfiglist: + Hiveconfig (" F Inance "," Equd "," hdfs://wdp.xxxxx.cn:8020/nifi/finance1/", Schema_json_equd_serde, Schema_equd);

?

Create IDXD data structure//define JSON structure val Schema_json_idxd_serde = "" "RetCode string, retmsg string, Data array<struct< indexid:string,= "" tradedate:date,= "" ticker:string,= "" Porgfullname:stri ng,= "" secshortname:string,= "" exchangecd:string,= "" precloseindex:double,= "" openindex:double,= "" Lowestindex: double,= "highestindex:double,=" "closeindex:double,=" "turnovervol:double,=" "turnovervalue:double,=" "chg:double , = "chgpct:double=" ">>" "; var schema_idxd =" "B.data.indexid, B.data.tradedate, B.data.ticker , B.data.porgfullname, B.data.secshortname, B.DATA.EXCHANGECD, B.data.preclose Index, B.data.openindex, B.data.lowestindex, B.data.highestindex, B.data.close Index, B.data.turnovervol, B.data.turnovervalue, B.data.chg, b.data.chgpct ""; Hiveconfiglist = hiveconfiglist: + HiveconFig ("Finance", "Idxd", "hdfs://wdp.xxxxx.cn:8020/nifi/finance1/", Schema_json_idxd_serde, SCHEMA_IDXD); 

?

Cyclic loading data in Def loaddatatohive (Args:hiveconfig) {val LoadPath = Args.hdfspath + args.modelname;    Val Tb_json_serde = "Json_serde_" + args.modelname + "_table";    Val tb= "Tb_" + args.modelname;        Val hivecontext = new Org.apache.spark.sql.hive.HiveContext (SC) if (args.database! = "" && args.schema! = "") {        Print ("Creating project ..." + args.modelname) hivecontext.sql ("Create DATABASE IF not EXISTS" + args.database);        Print ("Building extension model ..."); Hivecontext.sql ("CREATE TABLE IF not EXISTS" + Args.database + "." + Tb_json_serde + "(" + Args.schema + ") row format SE        Rde ' org.apache.hive.hcatalog.data.JsonSerDe ' location "+" ' "+ LoadPath +"/"); println ("CREATE TABLE IF not EXISTS" + Args.database + "." + TB + "AS-select" + ARGS.SCHEMA_TB + "from" + Args.databa        Se + "." + Tb_json_serde + "lateral VIEW explode (" + Tb_json_serde + ". Data) b as data"); Hivecontext.sql ("CREATE TABLE IF not EXISTS" + Args.database + "." + TB + "as select" +ARGS.SCHEMA_TB + "from" + Args.database + "." + Tb_json_serde + "lateral VIEW explode (" + Tb_json_serde + ". Data) B as        Data ");    println (Args.modelname + "extended model loading completed!");  }} hiveconfiglist.size; Hiveconfiglist.foreach {x = loaddatatohive (x)};

?

?

?

Importing HDFs data to Hive