Creating DB Links in Oracle

Syntax:

CREATE DATABASE LINK ABC.company.com
CONNECT TO schema/username
IDENTIFIED BY password
USING ‘ABC.company.com’;

1. ABC.company.com – name of DB link usually matches
2. schema/username – atleast one shema & username will be same
3. password
4. ‘ABC.company.com’ – Tnsping service name and find this one

Note: Don’t use single/double quotes for first 3 items.

Tableau Server Caching Layers

Tableau Server caching operates with layered approach, it contains below four layers
1. Tile Cache
2. Model Cache
3. Abstract Cache
4. Query Cache

It goes in Top down fashion, if top layer is missed then it approaches subsequent layer.

**1. Tile Cache**
In general, dashboards are delivered to users as a series of Image “Tiles” – these are assembled to show the complete dashboard.

We can use this cache if:
Same dashboard
No per-user security
Same dashboard size (Important! Don’t miss reusing cache for simple reason)

Handled by the gateway service, one per VizQL worker node

**2. Model Cache**
When re-rendering the dashboard we check to see if computations have already be done
calculated fields, table calculations, reference lines, trend lines, etc
We can use this cache if:
No change to data
No change to calcs
Model cache is RAM based per VizQL server instance

**3. Query Result Cache**
The query result cache holds the results from queries we have sent to data sources

We can use this cache if:
Dimensions and measures are the same
Filters are the same – this means no per user security
Cache has not expired or is not explicitly bypassed

Query result cache is RAM based per VizQL server instance

Source: https://community.tableau.com/docs/DOC-1466

Sqoop Error Message 2

ERROR:
ERROR org.apache.sqoop.tool.ImportTool – Import failed: Missing Hive MetaStore connection URI

Environment: While using Oozie in CDH5.14.2 Sqoop version 1.4.6

Cause: While doing Sqoop import with Parquet format throws this error in Sqoop version 1.4.6-CDH5.14.2.

Solution: To use other delimited format or by just leaving default format.

Sqoop Job Syntax with Incremental Load

1. sqoop job \
2. –create incremental_import_job \
3. — import \
4. –connect jdbc:mysql://localhost/company \
5. –username root \
6. –password password \
7. –table newemployee \
8. –target-dir /data/newemployee \
9. –incremental append \
10. –check-column id \
11. –last-value 7 \
12. –m 1

Book ref – Hadoop: Data Processing and Modelling

This books looks good to learn more about data ingestion.

Sqoop Error Message 1

ERROR:
ERROR org.apache.sqoop.tool.ImportTool – Import failed: There is no column found in the target table table_name. Please ensure that your table name is correct.

Cause: Sqoop is case sensitive with Oracle table names

Solution: TABLE_NAME instead of table_name

Note: Sqoop version 1.4.6

Column ordering in View Data Full data tab or download

Tableau orders the column Alphabetically based on the underlying table Field names.

1. Create a View with prefixes AA_,AB_ …etc. for the fields based on the order that is required.
2. Pull that view into Tableau and rename the fields as per reporting requirement.
3. Now the Full data tab/downloads will be ordered as per the requirement.
4. If any of the calculation need not show up in the Full data tab/downloads, just add simple table calculation function like “Calculation + (Index()-Index())”, it will automatically get discarded.

Note: Since we are creating View, we need not create a table with meaningless column names.

Materialized View REFRESH FAST ON COMMIT (Real-time/Live Refresh) – Same Environment no DB Links used

Below steps gives an example of creating fast refresh materialized views. “Fast Refresh” are the keywords used to create Real-time/Live MVs.

DB Links used to establish channel/route between DB server, eventually you can query remote DB with simple query like select * from table@remote.db.com

Step 1:
CREATE TABLE MV_TestTable(ID NUMBER, NAME VARCHAR2(25), CONSTRAINT MV_TestTable_pk PRIMARY KEY(ID));
Step 2:
CREATE MATERIALIZED VIEW LOG ON MV_TestTable WITH ROWID, PRIMARY KEY;
Step 3:
insert into MV_TestTable values (1,’one’);
insert into MV_TestTable values (2,’two’);
insert into MV_TestTable values (3,’three’);
insert into MV_TestTable values (4,’four’);
Step 4:
CREATE MATERIALIZED VIEW MVLIVE_TestTable
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS SELECT MV_TestTable.*,
MV_TestTable.ROWID AS Test_Table_rowid
FROM MV_TestTable;
Step 5:
Select * from MVLIVE_TestTable;
Returns 4 records
Step 6:
insert into MV_TestTable values (5,’three’);
insert into MV_TestTable values (6,’four’);
commit;
Step 7:
Select * from MVLIVE_TestTable;
Returns 6 records

Point to Ponder: All front-end applications and loader applications will commit the table after loading the data so this approach works very solid.