Big Query External Table Usuage

Usage:-

1. Read the data  from the file on Google storage  without loading it into Big query by treating the file as temporary table.
2. We can query the data of file  and also perform DML operations using this data of file.

Examples:-

1. Query the file data:-

bq --location=US query --external_table_definition=ORDER_DEL::INTEGRATION_ID:STRING@CSV=gs://cloud-dw-poc.appspot.com/PREPARED_LAYER/results-20180801-181812.csv 'SELECT INTEGRATION_ID FROM ORDER_DEL;'

2. Update the  Main table using the data of File.

bq --location=US query   --use_legacy_sql=false --external_table_definition=ORDER_DEL::INTEGRATION_ID:STRING@CSV=gs://cloud-dw-poc.appspot.com/PREPARED_LAYER/results-20180801-181812.csv 'UPDATE  `cloud-dw-poc.Z_NOT_USED.ORDER_DEL_NEW1` TGT SET   DELETE_FLG ="Y" FROM ORDER_DEL SRC  WHERE  TGT.INTEGRATION_ID =SRC.INTEGRATION_ID;'

3. Append to   Main table using the data of File.

bq query --append_table=true --batch=false --use_legacy_sql=false --destination_table=my-second-project-196504:DML_Operations.CRIME_DL --external_table_definition=TABLE::unique_key:INTEGER@CSV=gs://my-second-project-196504/CRIME.csv 'SELECT unique_key FROM TABLE;'

PWX-14607 Internal error: Invalid Serialization/mutex handle passed to function

There was space issue in the unix box. Once we freed the space, did not get this error again.

Informatica Metadata Query For Parameter File

SELECT DISTINCT

      B.SUBJECT_AREA,

      B.TASK_NAME AS SES_WF_NAME,

      A.ATTR_VALUE AS PRM_FILE_PATH

FROM

       REP_TASK_ATTR A,

       REP_ALL_TASKS B

WHERE

        A.ATTR_ID IN (1,4)

        AND A.TASK_ID = B.TASK_ID

        AND A.ATTR_VALUE IS NOT NULL

ORDER BY 1,2 ASC

-------------------------------------------------------------------------------------------------

SELECT
     *
 FROM
     (
         SELECT
             attr_id,
             b.subject_area,
             b.task_name    AS ses_wf_name,
             a.attr_value   AS prm_file_path,
             ROW_NUMBER() OVER(
                 PARTITION BY a.task_id
                 ORDER BY
                     a.version_number DESC
             ) AS rn
         FROM
             rep_task_attr a,
             rep_all_tasks b
         WHERE
             a.attr_id IN (
                 1,
                 4
             )
             AND a.task_id = b.task_id
             AND a.attr_value IS NOT NULL
             AND b.subject_area = 'RAW_DATA'
     )
 WHERE
     rn = 1
     

Oracle Data Integrator Array Fetch Size and Batch Update Information

Important Links

Oracle Support
Batch Update and Array Fetch Values Impact on the Performance of ODI Processes (Doc ID 424482.1)

Oracle Data Integrator 11g and 12c Repository details

Refer to oracle support:-

Oracle Data Integrator 11g and 12c Repository Description (Doc ID 1903225.1)

List of Useful Oracle Support Docs

Discoverer

1. Discoverer 11g Querying Against An E-Business Suite 12 Multi-Org VPD Protected View With ORG_ID Returns No Data (Doc ID 732826.1)
2. How To Run SQL From A Discoverer Applications Mode Workbook In SQL*PLUS (SQLPLUS) To Troubleshoot Performance And Worksheet Results (Doc ID 279736.1)
3. How to query in SQL for org-specific data in a MOAC environment (Doc ID 415860.1)
4. How To Retrieve Rows From Table Or Synonym For An ORG_ID In E-Business Suite 12 (Doc ID 787677.1)

OBIEE

1. OBIEE 11G: Integrating Oracle Business Intelligence Applications (OBEE) With Oracle E-Business Suite (Doc ID 1343143.1)
2. Can You Migrate An E-Business Suite 12 Discoverer End User Layer ( EUL ) to OBIEE? ( Doc ID 1066087.1 ) 
3. 2.6.1 Oracle BI Applications Authorization for Oracle EBS
   http://docs.oracle.com/cd/E20490_01/bia.7963/e19042/security.htm#CJAHFDJJ 
4. Applying Data Access Security to Repository Objects
   http://docs.oracle.com/cd/E28280_01/bi.1111/e10540/dataaccess.htm#CHDJGHAH 
5. https://docs.oracle.com/cd/E23943_01/bi.1111/e10543/privileges.htm#BIESC6053 

ODI





Database

Oracle Data Integrator Best Practices: Using Reverse-Engineering on the Cloud and on Premises

http://www.ateam-oracle.com/oracle-data-integrator-best-practices-using-reverse-engineering-on-the-cloud-and-on-premises/