Cloudera Community
roshanbi
user rank
Contributor
My Accepted Solutions
Show All

Re: extract xml fields

by Contributor in Support Questions
‎08-07-2021 08:54 AM
‎08-07-2021 08:54 AM
Hi,   no it won't work. ... View more

sql functions

by Contributor in Support Questions
‎08-07-2021 08:44 AM
1 Kudo
‎08-07-2021 08:44 AM
1 Kudo
Dear Team,   is there a way I can convert Oracle functions to Zeplin (Kudu) equivalent?   Below query calls function "apps.xxmt_getvendor_emailadd (ai.vendor_site_id)"   SELECT sysdate, apps.xxmtg_get_org_name (aip.org_id) operating_unit, ai.creation_date inv_creation_date, ai.invoice_date, ai.invoice_received_date Inv_RecvDte, aba.batch_name, ai.invoice_num, ai.description invoice_desc, ai.invoice_amount, aps.payment_priority, aps.due_date, ac.currency_code, ac.creation_date pmt_creation_date, apps.X_MTG_get_user_name(ac.created_by) created_by, ac.status_lookup_code, ac.check_date document_date, ac.check_number, aip.amount pmt_doc_amount, pd.payment_document_name, ac.bank_account_name, ac.VENDOR_NAME Supplier_Name, pov.segment1 SupplierNum, b.bank_name Supplier_Bank, to_char(b.bank_number) Supplier_Banknum, br.bank_branch_name Supplier_BankBranch, iby_ac.bank_account_num Supplier_BankAccount, apps.xxmt_getvendor_emailadd (ai.vendor_site_id) supplier_email,trunc(ac.CREATION_DATE) Payment_Creation_Date, ...   Oracle function <apps.xxmt_getvendor_emailadd> content: create or replace Function xxmt_getvendor_emailadd(P_Vendor_Site_Id In Number) Return Varchar2 Is LvEmailaddress Varchar2(240):=NULL; Begin Select email_address Into LvEmailaddress From ap_supplier_sites_all Where vendor_site_id=P_Vendor_Site_Id; Return LvEmailaddress; Exception When Others Then LvEmailaddress:=''; Return LvEmailaddress; End xxmt_getvendor_emailadd;   Thanks, Roshan ... View more

Re: concat string and null

by Contributor in Support Questions
‎07-13-2021 12:09 AM
‎07-13-2021 12:09 AM
On Oracle below works select concat(nvl(cast(glb.budget_version_id as varchar2(10)),0), cast(glc.segment1 as varchar2(10))) as balid from gl.gl_balances glb, gl.gl_code_combinations glc, gl.gl_ledgers glg ;   Impala   ... View more

concat string and null

by Contributor in Support Questions
‎07-13-2021 12:00 AM
‎07-13-2021 12:00 AM
Hello Team,   can you please advise how to replace nulls by a default value?   select concat(cast(glb.budget_version_id as string)," ", cast(glc.segment1 as string)) as balid from oracle_financial.gl_balances glb, oracle_financial.gl_code_combinations glc, oracle_financial.gl_ledgers glg ;   If I add a null to a value, null will be returned.   I tried NVL it is not working.   Thanks,   Roshan   ... View more

mask fields

by Contributor in Support Questions
‎07-02-2021 04:16 AM
‎07-02-2021 04:16 AM
Dear Team,   how can I mask last 5 digits in field below in Kudu?   on Ranger? Thanks,   Roshan ... View more

extract xml fields

by Contributor in Support Questions
‎07-01-2021 04:30 AM
‎07-01-2021 04:30 AM
Hello Team,   how can we extract XML values from the column below (type string) from Impala (Kudu)   Regards,   Roshan ... View more
Labels:

change datatype column

by Contributor in Support Questions
‎06-28-2021 10:41 PM
‎06-28-2021 10:41 PM
Hi,   is there a way we can change the datatype from int to double for a column on Impala? Table size is around 3 billion records. I do not plan to drop and recreate it.   ALTER TABLE cbs.gprs_home_cdrs CHANGE percentage_val_n percentage_val_n double; AnalysisException: Cannot change the type of a Kudu column using an ALTER TABLE CHANGE COLUMN statement: (INT vs DOUBLE) impalad version 3.4.0-SNAPSHOT RELEASE (build 134517e42b7b6085e758195465f956f431e0e575) Built on Sat Dec 12 11:15:02 UTC 2020 Version: Cloudera Enterprise 7.1.3 (#4999720 built by jenkins on 20200805-1701 git: fa596184790377f07ba80e9cd4da8b875237939c) Java VM Name: OpenJDK 64-Bit Server VM Java Version: 11.0.10   Thanks,   Roshan ... View more

Re: RDD questions

by Contributor in Support Questions
‎06-27-2021 03:51 AM
‎06-27-2021 03:51 AM
What does the code below do? val conf = new SparkConf().setMaster("local").setAppName("testApp") val sc= SparkContext.getOrCreate(conf)   Reference: https://www.educba.com/spark-rdd-operations/ ... View more

Re: How to import xml data from oracle to hive usi...

by Contributor in Support Questions
‎06-27-2021 12:41 AM
‎06-27-2021 12:41 AM
Hi,   I run the following command and I am getting error below: sqoop import --connect "jdbc:oracle:thin:@10.215.227.*:1521:cxx_stby" --username cbs_view --password  ccccc --query "select TMP_ACCOUNT_CODE_N,decode(EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@STREET_DESC'),'.',null,EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@STREET_DESC'))||' '||EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@SUB_LOCALITY_DESC') ||' '||EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@CITY_DESC') "ADDRESS" from tmp_address_xml@cbsstandby where $CONDITIONS" -m 4 --split-by object_type --hive-import --target-dir '/devsh_loudacre' --hive-table test_oracle.address_tab --verbose   SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/3.0.1.0-187/hadoop/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/3.0.1.0-187/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] 21/06/27 07:39:14 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7.3.0.1.0-187 21/06/27 07:39:14 DEBUG tool.BaseSqoopTool: Enabled debug logging. 21/06/27 07:39:14 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 21/06/27 07:39:14 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override 21/06/27 07:39:14 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc. 21/06/27 07:39:14 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory 21/06/27 07:39:14 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.DefaultManagerFactory 21/06/27 07:39:14 DEBUG sqoop.ConnFactory: Trying ManagerFactory: org.apache.sqoop.manager.oracle.OraOopManagerFactory 21/06/27 07:39:14 DEBUG oracle.OraOopUtilities: Enabled OraOop debug logging. 21/06/27 07:39:14 DEBUG oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop can be called by Sqoop! 21/06/27 07:39:14 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled. 21/06/27 07:39:14 DEBUG sqoop.ConnFactory: Trying ManagerFactory: org.apache.sqoop.manager.DefaultManagerFactory 21/06/27 07:39:14 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin:@10.215.227.22:1521 21/06/27 07:39:14 DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache. 21/06/27 07:39:14 INFO manager.SqlManager: Using default fetchSize of 1000 21/06/27 07:39:14 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@3bf7ca37 21/06/27 07:39:14 INFO tool.CodeGenTool: Beginning code generation 21/06/27 07:39:14 ERROR tool.ImportTool: Import failed: java.io.IOException: Query [select TMP_ACCOUNT_CODE_N,decode(EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@STREET_DESC'),'.',null,EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@STREET_DESC'))||' '||EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@SUB_LOCALITY_DESC') ||' '||EXTRACTVALUE (address_x, '//ADDRESS_DTLS/@CITY_DESC') ADDRESS from tmp_address_xml@cbsstandby where ] must contain '$CONDITIONS' in WHERE clause. at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:333) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1879) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1672) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:516) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:656) at org.apache.sqoop.Sqoop.run(Sqoop.java:150) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:186) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:240) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:249) at org.apache.sqoop.Sqoop.main(Sqoop.java:258)   Kindly advise   Thanks,   Roshan ... View more

Re: Impala query

by Contributor in Support Questions
‎06-26-2021 07:18 AM
‎06-26-2021 07:18 AM
Hi @aakulov    thanks for the update.   Can you please advise how can I schedule this scoop job so that it will update the hive table with incremental changes(CDC)? for example, suppose the XML fields are updated on Oracle, how can I schedule scoop job to replicate the incremental changes on Hive and Kudu?   Regards,   Roshan ... View more
Contact Me
Online
Offline
Last Visited
‎07-11-2022 05:29 AM
Community Statistics
Member Since ‎09-19-2020 08:57 AM
Last Visited ‎07-11-2022 05:29 AM
Posts 46
Kudos received 1