Import Java.sql.DriverManagerimport Java.util.Propertiesimport com.zhaopin.tools. {dateutils, Textutils}import org.apache.log4j. {level, Logger}import org.apache.spark.sql.sparksession/** * Created by XiaoYan on 2018/5/21. */object IHRDOWNLOADPG {def main (args:array[string]) {//Set Spark log Level Logger.getlogger ("Org.apache.spark"). SetLevel (level.error) System.setproperty ("Hadoop_user_name", "Hive") val spark = sparksession. Builder (). Master ("L Ocal[*]. AppName ("Hive->> ihr_oper_download"). config ("Spark.sql.warehouse.dir", "Spark-warehouse") . config ("Hive.metastore.uris", "thrift://master:9083"). Enablehivesupport (). Getorcreate () Import Spark.sql Val dt = if (!args.isempty) args (0) Else "20180506" val yesterday = dateutils.dateadd (dt,-1) Val url = "Jdbc:pos Tgresql://192.168.9.222:5432/safe_base "Class.forName (" Org.postgresql.Driver ") Val conn = Drivermanager.getconnecti On (URL, "Secu_man", "Secu_man") val stmt = CoNn.createstatement () stmt.execute ("Delete from ihr_oper_download where dt = '" + yesterday+ "'")//Query Rdd val re1 = SQL ("Select Oper_date," + "acct_id," + "acct_name," + "module_name," + " Oper_desc, "+" IP, "+" DT "+" from Safe.fact_ihr_oper_download T "+" where T.dt > ' 20180320 ' and T.dt < "+yesterday+"); Val connectionproperties = new Properties ()//Increase the database username (user) password (password), specify PostgreSQL driver (driver) Connectionpropertie S.put ("User", "Secu_man"); Connectionproperties.put ("Password", "Secu_man"); Connectionproperties.put ("Driver", "org.postgresql.Driver"); RE1.TODF (). Write.mode ("append"). JDBC (URL, "Ihr_oper_download", connectionproperties); System.err.print ("ihr_oper_download insert complete!!"); }}
NOTE: If the PG table does not exist, a table is automatically created by default and the field type is text
Spark SQL queries hive table and writes to PG