FILE IS AWS S3 ??
CREATE EXTERNAL STAGE ??
CREATE EXTERNAL STAGE STG_NM
URL =
FILE_FORMAT =
STI=;
FF STI
ESTABLISHING THE CONNNECTION
BASED ON TEH FILE TABLE
LIST @STG;
COPY INTO TB
FROM @STG;
ON_ERROR =CONTINUE ;
ON_ERROR =ABORT_STATEMENT ;
PURGE =TRUE;
RM COMMAND ;
SELECT $50,METADATA$FILENAME
FROM @STG;
ONE FILE CAN WE LOAD ONE MORE TIME??
TRUNCATE ?? LOAD METADATA HISTORY WILL BE REMOVED
FORCE =TRUE ??
FILENAME
TABLENAME
NO OF RECORDS PROCESES ERROR RECORDS SIZE?
COPY 64 DAYS
SNOWIPE 14 DAYS
CAN WE USE WHERE CLAUSE IN COPY STTAEMENT??NO
--------------
I WANT TO AOUTOMATE THIS POCRSS??
SNOWPIPE AUTO_INGEST =TRUE
SQS - SIMPLE QUEUE SERVCIE
EVENT NOTIFICATION
CAN WE CREATE SP ON INTERNAL STAGES???
AUTO_INGEST =FALSE
NAMED INTERNAL STAGE
TABLE STAGE ;
ALTER PIPE PIPE_NAME REFRESH;
WE CAN ABLE TO CREATE SNOWPIPE ON INTERNAL STAGES: TABLE STAGE AND NAMED STAGE;
AUTO_INGEST =FALSE
ALTER PIPE PIPE_ANM REFRESH;
SNOWFLAKE ->TRIGGER ->SNPWPIP =>COPY ->SF TABLE;
WITHOUT LAING I WANT TO ACCES FROM SF FILE IS THERE IN AWS AZURE OR GCP ??
EXTERNAL TABLE??
DML ?? NO
STORAGE COST ?? NO
WILL HOLD : METADATA INFORMATION OF FILES ??
METADATA$FILENAME
METADATA$FILE_ROW_NUMBER
CAN WE USE WHERE CLUASE IN EXTERNAL TABLE ?? YES
CAN WEPERFOM JOINS WITH OTHER TABLE WITH EXTERNAL TABLE ->YES
--------------------------
DATA LOAIDNG:
S3/ADLS/CLOUD STORAGE OR LOCLA MAHINE -> SF TBALE
S3 ->SF -> TRANSFOMRED DATA ->
COPY INTO TB
FROM @STG- INT/EXT ;
DATA UNLOAIDNG:
COPY INTO @STG
FROM TRANFORMED_TB;
USER_ID AND PWD ->
SSO ->SINGLE SIGN ON
MFA -> MULTI FACTOR AUTHENTICATION
CLOUD -> AWS
REPORTING TOOL > POWER BI
ETL TOOL -> MATILLION AWS GLUE
HOW THE WORK ->JIRA TICKET
CALL ->SCRUM CALL ?? TIMING->10 TO 15MNS??
10AM
11AM
5PM 6PM ;
USER STORIES
AGILE METHOLDOGY
CODE REPOSITORY -> GITHUB
CI CD PIPELENS ->CONTINOUS INTEGRATION
CONTINOUS DEPLOYMENT ->dEVOPS -JENKINS TOOL
DATA SOURCE: OLTP SYSTEMS - SAP HANA ORACLE
TERA BYTES TO PETA BYTES
-------------------
USE DATABASE DEV_DB;
USE SCHEMA RAW_SCHEMA;
--
5 SF DEV
1 POWER BI SF ACCOUNT
1 SF ADMIN
1 SF ARCHITECT
100 TABLES
5 NAMED STAGES
10 EXTERNAL STAGE;
SHOW STAGES;--> 15 ?? NAMED INTERNAL AND NAMED EXTERNAL STAGE
TOTALLLY HOW MANY STAGES ??123 USER 8
TABLES ->10 ??
;
s3://s3-csv-files123
USE DATABASE DEV_DB;
USE SCHEMA RAW_SCHEMA;
--
5 SF DEV
1 POWER BI SF ACCOUNT
1 SF ADMIN
1 SF ARCHITECT
100 TABLES
5 NAMED STAGES
10 EXTERNAL STAGE;
SHOW STAGES;--> 15 ?? NAMED INTERNAL AND NAMED EXTERNAL STAGE
TOTALLLY HOW MANY STAGES ??123 USER 8
TABLES ->10 ??
;
LIST @STG_NM;
SELECT $1,$2 FROM @STG_NM/afr.txt;
STG ->FILE FORMAT ->SKIP_HEADER=1;
SHOW STAGES;
DESC STAGE STG_NM;
DESC FILE FORMAT CSV_FF_HEADER;
SHOW TABLES;
SELECT * FROM COUNTRY;
LIST @EXT_STG1;
RM @EXT_STG1;
COPY INTO @EXT_STG1
FROM COUNTRY;---WHAT IS THE FILENAME IT CONTAINS DATA_0_0_0
SHOW TABLES;
COPY INTO @EXT_STG1
FROM COUNTRY;
--Files already existing at the unload destination: @EXT_STG1. Use overwrite option
to force unloading.
COPY INTO @EXT_STG1/LLL
FROM EM
OVERWRITE =TRUE
HEADER=TRUE;
LIST @EXT_STG1;
SELECT $1,$2
FROM @EXT_STG1;
HEADER = TRUE ;
SHOW TABLES;
SELECT * FROM EMP1;
COPY INTO @EXT_STG1
FROM EMP1;
RM @EXT_STG1;
LIST @EXT_STG1;
HUGE DATA ??SF WILL DO PARLLEL THREADS;
SINGLE FILE ??;
COPY INTO @EXT_STG1
FROM EMP1
SINGLE =TRUE;
LIST @EXT_STG1;
WE ARE FEEDING THE DATA TO DONWSTREAM GUYS IN THE FORM OF FILES
AI ML DS ->ALGRITHM LLM ;
------------------------------------------
ORACLE(5 TABLES) -> S3->SF -> TRANFOMEED ->SINGLE_TABLE -> ??
DATA UNLOAIDNG
DATA SHARING
VIEWS
WHAT TYPE OF TRANSFOMATION
DATA TYPES
NULL HANDLING
DATE CONVERSION
AGGREGATE FUNCTIONS
ANALAYTICAL FUNCTIONS
CASE
JOINS
SET OPEARTEOS
VIEWS??? VIRTUAL TABLE
SECURITY ;
NORMAL VIEW
MATERIALIED VIEW
SECURE VIEW
SHOW VIEWS;
SHOW TABLES;
CREATE VIEW V1_EM AS
SELECT * FROM EM;
CREATE MATERIALIZED VIEW V1_MV_EM AS
SELECT * FROM EM;
CREATE SECURE VIEW V1_S_EM AS
SELECT * FROM EM;
CREATE SECURE MATERIALIZED VIEW V1_SMV_EM AS
SELECT * FROM EM;
SHOW VIEWS LIKE 'V1%M';
IS_sECURE
IS_MATERIALIZED ;
SHOW MATERIALIZED VIEWS;
SELECT * FROM V1_EM;
SHOW VIEWS LIKE 'V1%';
DMLS ON VIES;
DELETE FROM V1_EM;
DELETE FROM V1_MV_EM;
DMLS ARE NOT ALLOWED;
CREATE TABLE T114(ID NUMBER);--3
INSERT INTO T114 VALUES(1),(2),(3);
CREATE TABLE T1144(ID1 NUMBER);--0
SELECT * FROM T114 INNER JOIN T1144;--CROSS JOIN T1*T2= 3*0=0
CREATE OR REPLACE VIEW MV2
AS
SELECT * FROM T114 INNER JOIN T1144;
CREATE OR REPLACE MATERIALIZED VIEW FMV2
AS
SELECT * FROM T114 INNER JOIN T1144;
Invalid materialized view definition. More than one table referenced in the view
definition
;
MVS CAN BE CREATE ON SINGLE TABLE;
CLONING?? ;
SELECT * FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE TABLE_NAME IN ('V1_EM','V1_MV_EM','V1_SMV_EM','V1_S_EM');
MVS WILL HAVE STORAGE ;
SHOW MATERIALIZED VIEWS;
5 DEV 1 ADMI 1 SF ARCHIRCT 10 TABLE 10NMD STGA
10 EXTRRNAL SAGE;
SHOW STAGES;10+10;
DATA MIGRATION PROJECT:
DB :STAR AND SNOWFLAKE SHCEMA ;
STAR ??
DEVELOPING THE APPLICATION IN SF ??
STAR SCHEMA AND SNOWFLAKE SCHEMA
FACTS AND DMINESIONS;
DOING TRANDOM STAR AND SNOWFLAKE
