Shulou(Shulou.com)06/03 Report--

In the past, Sqoop was used to extract data from the generated hdfs data store to the oracle database: sqoop extraction statement:

Sqoop export-connect "jdbc:oracle:thin:@ip:port:sid"-username user name-password password-table sid. Table name-- export-dir hdfs://nameservice1/user/XXX (hdfs address)-- fields-terminated-by "\ 001"-- null-non-string'--null-string'- m 10

Due to the project requirements, we now need to write back to the oracle directly after completing the fields required by the provincial capital in the code. Because the amount of data is very large every day, there are great limitations in using instances or List, and unexpected things such as memory exceptions may occur, so I use the cache mechanism to store data. Then write back the temporary table that generates the result directly (the later hbase interface encapsulates the batch submission is also similar) without much nonsense, go directly to the code: 1. Establish the cache entity package usi.java.oracle.

/ * *

@ author HK

@ date 2011-2-15 06:45:57

, /

Public class Cache {

Private String key

Private Object value

Private long timeOut

Private boolean expired

Public Cache () {

Super ()

}

Public Cache (String key, String value, long timeOut, boolean expired) {

This.key = key

This.value = value

This.timeOut = timeOut

This.expired = expired

}

Public String getKey () {

Return key

}

Public long getTimeOut () {

Return timeOut

}

Public Object getValue () {

Return value

}

Public void setKey (String string) {

Key = string

}

Public void setTimeOut (long l) {

TimeOut = l

}

Public void setValue (Object object) {

Value = object

}

Public boolean isExpired () {

Return expired

}

Public void setExpired (boolean b) {

Expired = b

}

}

2. Set up cache controller

Package usi.java.oracle

Import java.util.Date

Import java.util.HashMap

/ * *

@ author HK

@ date 2011-2-15 09:40:00

, /

Public class CacheManager {

Private static HashMap cacheMap = new HashMap ()

/ * *

This class is singleton so private constructor is used.

, /

Private CacheManager () {

Super ()

}

/ * *

Returns cache item from hashmap@param key

@ return Cache

, /

Private synchronized static Cache getCache (String key) {

Return (Cache) cacheMap.get (key)

}

/ * *

Looks at the hashmap if a cache item exists or not@param key

@ return Cache

, /

Private synchronized static boolean hasCache (String key) {

Return cacheMap.containsKey (key)

}

/ * *

Invalidates all cache

, /

Public synchronized static void invalidateAll () {

CacheMap.clear ()

}

/ * *

Invalidates a single cache item

@ param key

, /

Public synchronized static void invalidate (String key) {

CacheMap.remove (key)

}

/ * *

Adds new item to cache hashmap@param key

@ return Cache

, /

Private synchronized static void putCache (String key, Cache object) {

CacheMap.put (key, object)

}

/ * *

Reads a cache item's content@param key

@ return

, /

Public static Cache getContent (String key) {

If (hasCache (key)) {

Cache cache = getCache (key)

If (cacheExpired (cache)) {

Cache.setExpired (true)

}

Return cache

} else {

Return null

}

}

/ * *

@ param key@param content

@ param ttl

, /

Public static void putContent (String key, Object content, long ttl) {

Cache cache = new Cache ()

Cache.setKey (key)

Cache.setValue (content)

Cache.setTimeOut (ttl + new Date (). GetTime ())

Cache.setExpired (false)

PutCache (key, cache)

}

/ * @ modelguid {172828D6-3AB2-46C4-96E2-E72B34264031} /

Private static boolean cacheExpired (Cache cache) {

If (cache = = null) {

Return false

}

Long milisNow = new Date () .getTime ()

Long milisExpire = cache.getTimeOut ()

If (milisExpire

< 0) { // Cache never expires return false; } else if (milisNow >

= milisExpire) {

Return true

} else {

Return false

}

}

}

3. Establish a data object that needs to be exported

Package usi.java.oracle

Public class TaskAll {

Private String mme_eid

Private String mme_editor

Private String entitytype_eid

Private String project_eid

Private String resource_eid

Public String getMme_eid () {

Return mme_eid

}

Public void setMme_eid (String mme_eid) {

This.mme_eid = mme_eid

}

Public String getMme_editor () {

Return mme_editor

}

Public void setMme_editor (String mme_editor) {

This.mme_editor = mme_editor

}

Public String getEntitytype_eid () {

Return entitytype_eid

}

Public void setEntitytype_eid (String entitytype_eid) {

This.entitytype_eid = entitytype_eid

}

Public String getProject_eid () {

Return project_eid

}

Public void setProject_eid (String project_eid) {

This.project_eid = project_eid

}

Public String getResource_eid () {

Return resource_eid

}

Public void setResource_eid (String resource_eid) {

This.resource_eid = resource_eid

}

}

5. Execute logical body, write back data, and submit in batch

Package usi.java.oracle

Import java.sql.Connection

Import java.sql.DriverManager

Import java.sql.PreparedStatement

/ / import java.sql.ResultSet

Import java.util.List

Import org.apache.spark.SparkConf

Import org.apache.spark.SparkContext

Import org.apache.spark.sql.DataFrame

Import org.apache.spark.sql.Row

Import org.apache.spark.sql.hive.HiveContext

Public class redict_to_171ora {

Public static void main (String [] args) {

SparkConf sc = new SparkConf () .setAppName ("redict_to_171ora")

SparkContext jsc = new SparkContext (sc)

HiveContext hc = new HiveContext (jsc)

String hivesql1= "select t.mmeroomeidretemt.mmeuploeditor.entitytypeaccouneidreport.projectaccouneid.resourceSecreteid from usi_odso.c_taskall t limit 150000"

DataFrame redict_to_171ora= hc.sql (hivesql1); / / redict_to_171ora.registerTempTable ("hivesql1"); List collect=redict_to_171ora.javaRDD (). Collect (); int osteno; for (Row lists: collect) {TaskAll task=new TaskAll (); task.setMme_eid (lists.getString (0)); task.setMme_editor (lists.getString (1)); task.setEntitytype_eid (lists.getString (2)) Task.setProject_eid (lists.getString (3)); task.setResource_eid (lists.getString (4)); CacheManager.putContent (o + ", task, 30000000); System.out.println (lists.size ()); System.out.println (lists.getString (0)); System.out.println (lists.getString (1)); System.out.println (lists.getString (2)) System.out.println (lists.getString (3)); System.out.println (lists.getString (4)); * /} System.out.println (o); Connection con = null;// create a database connection PreparedStatement pre = null;// create a precompiled statement object, which is usually used instead of Statement / / ResultSet result = null / / create a result set object try {Class.forName ("oracle.jdbc.driver.OracleDriver"); / / load the Oracle driver System.out.println ("start trying to connect to the database!") ; String url = "jdbc:oracle:" + "thin:@ip:1521:sid"; / / 127.0.0.1 is the local address, and XE is the default database name of the compact version of Oracle String user = "user"; / / user name, the default account name of the system String password = "password"; / / the password con = DriverManager.getConnection (url, user, password) you chose during installation / / get the connection System.out.println ("connection succeeded!") ; String sql = "insert into c_taskall_test (mme_eid,mme_editor,entitytype_eid,project_eid,resource_eid) values"; / / precompiled sentences, "?" The representative parameter pre = con.prepareStatement (sql); / / instantiate the precompiled statement for (int item0witi)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.