Does any "Best Practices" or "Step by Step" for oracle to bigquery with replicate task sample ?

[khchoy]

Hi All,

New Compose supported several Cloud DW Database but I cannot find  any related documentation .

Compose work with Replicate task for apply change data.  

But User Guide is not help full for create sample and testing.

Does anyone have more useful documents ?

Regards,

Kwangho 

[TimGarrod]

Hi @khchoy , 

Documentation for Compose's May release can be found here (this is the Data warehouse section of the documentation)

https://help.qlik.com/en-US/compose/May2021/Content/ComposeDWDL/Main/DW/General/dw_project_landing.h... 

There is a project tutorial walking you through the basics (and it provides a link to a sample SQL server source database - Northwind) - https://help.qlik.com/en-US/compose/May2021/Content/ComposeDWDL/Main/DW/Get%20Started/compose_tutori... 

Hope this helps!

[khchoy]

Help Page  is not helpful for testing with Bigquery and Sample page also not helpful because  BigQuery only support full load and store change task for Compose.

Regards,

KwangHo 

[TimGarrod]

Hi @khchoy  - 

If the requirement is to simply send data to BigQuery and APPLY the changes (i.e. straight replication), then FULL load, Apply changes and store changes are applicable in the Replicate task for BigQuery.

With respect to Compose - it is typically recommended to perform only FULL LOAD and STORE CHANGEs for ANY DW platform.   This reduces the amount of processing required by Replicate and Compose will handle apply data into the specific data warehouse model based on the ETL mappings.   However you CAN use APPLY changes in the Replicate task definition, if that fits your requirements and use case.

If you can be more specific in your question then I can provide prescriptive advice.  Saying "help page is not helpful" doesn't tell me where you are having issues.   

The concepts for Qlik Compose are the same regardless of the cloud / DW platform you are running on as Compose obfuscates the physical table creation and ELT code generation (which is specific to each platform). 

As with other DW technologies, to configure / work with BigQuery in Qlik Compose - 

1.    Create a Data Warehouse Project
2.    Connect to BigQuery using JSON credentials for your Service Account Key created / downloaded from Google clouds IAM services.
3. Define your DW model / run through the Discovery process
4. Create the DW tables using Compose
5. Create ETL mappings (if required as mappings can be autogenerated when Discovery is used)
6. Generate ETL mappings, execute and review the data in your data warehouse
7. Design Data mart, create tables , generate code and Execute the load processes

Compose can process data delivered by Replicate or any data that resides in your DW environment and is delivered by another data ingestion process.

[khchoy]

Hi TimGarrod,

Thank for kindly answer.

I try to many time 3 an 4 steps on your guide. but every time  I  met error of type mismatch.

it is number type (I changed "Expose NUMBER as: " to "NUMERIC(38,7)" in replicate oracle source endpoint )

I'm searching google and Qlik partner learning  site but I can't find any helpful document of Compose.

I have old trainning documents of Compose DW  but "create DW "  step file is empty file.

So I ask someone have useful documents.

Regards,

KwangHo 

[TimGarrod]

Ah. Ok - so Replicate sometimes will convert large numerics for FLOATS. FLOAT, being an imprecise data type is not currently supported in Compose for BigQuery. So it is recommended to run a global transform to take any REAL4 / REAL8 Replicate data types and convert to NUMERIC(38,9). In BigQuery - every numeric is a 38,9 as they don't support providing a specific precision and scale.

If you do that, then you won't get the data type mismatch between landing area and data model.

[khchoy]

Thanks .

I don't know why data warehouse step of Compose project met error.

I just test with one table  and just follow steps Compose User Guide. Database manage and model steps seems OK. I can't go next step.  I asked some documents to Qlik guys but they did not have documents and answer is "using  User Guide".

test table is very simple and I just want to create same layout table in bigquery. 

CREATE TABLE TEST.EMPLOYEES
(
EMPLOYEE_ID NUMBER NOT NULL,
FIRST_NAME VARCHAR2(255 BYTE) NOT NULL,
LAST_NAME VARCHAR2(255 BYTE) NOT NULL,
EMAIL VARCHAR2(255 BYTE) NOT NULL,
PHONE VARCHAR2(50 BYTE) NOT NULL,
HIRE_DATE DATE NOT NULL,
MANAGER_ID NUMBER(12),
JOB_TITLE VARCHAR2(255 BYTE) NOT NULL,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
)
LOGGING
NOCOMPRESS
NOCACHE;

Regards,

Kwangho
ALTER TABLE TEST.EMPLOYEES ADD (
PRIMARY KEY
(EMPLOYEE_ID)
ENABLE VALIDATE);

[TimGarrod]

Are you just trying to replicate data to the target (BigQuery) or do you have a DW use case?

If you just want to Replicate data - (copy the source to BigQuery in the same form and keep in-synch) then just use Replicate with FULL and APPLY CHANGES.     The FULL and STORE is a recommended (but not required) config for using Compose for a full on DW use case.

[khchoy]

Hi,

First of all, we are preparing to create introduction materials, demonstration materials, and simple technical documents for market development. However, I had no experience in using it and there was no suitable similar material, so I asked the branch and the APAC person in charge, but because there was no data, I inquired if people with dignity in the community had simple materials.

Regards,

KwangHo

[kennedystewart]

Can anyone send me the document please. I am also in need. Thanks in advance.                                                    Geometry Dash