Fast Export vs Fast Load (Teradata)

**************
FAST EXPORT
**************

• Used to export data from Teradata into flat files
• Can generate data in report format
• Data can be extracted from one or more tables using join
• Deals in block export (Useful for extracting large volumes)
• Is the ability to ship data over multiple session connections simultaneously thereby leveraging the total connectivity available between the client platform and the database engine. In order to do this,Fastexport spends more resources on executing the query in order to prepare the blocks in such a way that when they are exported over multiple sessions they can easily be reassembled in the right order by the client without additional sorting or processing of the rows.

rank vs dense_rank vs row_number with partition

• One of the most obvious and useful set of window functions are ranking functions where rows from your result set are ranked according to a certain scheme. 
• There are three ranking functions: ROW_NUMBER() RANK() DENSE_RANK() 

DDL Scripts

Create table dup_employees
(
     id int,
     first_name nvarchar(50),
     last_name nvarchar(50),
     gender nvarchar(50),
     salary int
);
Insert into dup_employees values (1, 'Mark', 'Hastings', 'Male', 60000);
Insert into dup_employees values (1, 'Mark', 'Hastings', 'Male', 60000);
Insert into dup_employees values (1, 'Mark', 'Hastings', 'Male', 60000);
Insert into dup_employees values (2, 'Mary', 'Lambeth', 'Female', 30000);
Insert into dup_employees values (2, 'Mary', 'Lambeth', 'Female', 30000);
Insert into dup_employees values (3, 'Ben', 'Hoskins', 'Male', 70000);
Insert into dup_employees values (3, 'Ben', 'Hoskins', 'Male', 70000);
Insert into dup_employees values (3, 'Ben', 'Hoskins', 'Male', 70000);

SQL Basics

SELF JOIN

• A self join is a join in which a table is joined with itself

Ex :when we want to get the manager name who is an employee then in the join condition left side would be the first table (employee e) and right side would be the second table (employee m) in the join condition.

Note: It would take manger_id from the employee (m) table and would look for the employee_id in the  employee table (e)

Select  id,e.employee_name,m.employee_name
from employee e
Join employee m
on e.manger_id=m.employee_id

MAPR Products

MAPR                                 :  Started in stealth mode  (2009)
MapR-FS                            :  It is a Hadoop compatible file system
MapR-DB                           :  It is the first in house DB that ran on the same technology stack
Apache-Drill                       :  First schema free analytics
MAPR-Streams                  : Was introduced for global event processing
Converged Data platform :  Branded all the above products into converged data platform.
                                                Only converged data platform in the industry.
                                                Supports all the data
                                                Runs on every cloud on premise and on the edge
                                                It has highly available design
                                                Capability of global data fabric
                                                It has global database
                                                It has global event streaming engine
                                                Operates at unlimited scale
                                                It supports file, tables, document and streams
                                                It supports docker container data platform to make it highly available
                                                The file system in mapr is different from others

Aws cloud formation

Is a service that helps you model and set up our amazon web services resources so that we can spend less time managing those resources and more time focusing on our applications that run on AWS

TEMPLATES
We can also create templates in AWS cloud formation.We can use designers for creating this template and save this template

•  To create a cloud formation script we need a JSON script.This can also be created using cloud formation designer as shown below. When we drag  resource JSON script would be generated.

Apache Accumulo

 Is a robust database scalable data storage and retrieval system based on google's big table design and built on top of Apache Hadoop, Zookeper and Thrift.
It's improvements on big table design are:

• Server-side programming mechanism that can modify key/value pairs
• Cell based access control

Ingestion in GCP

In the figure x axis is what is closer to GCP and y axis is the amount of data
These are the different approaches which we can take for data ingestion to GCP

TRANSFER SERVICE
Storage Transfer Service allows you to quickly import online data into Cloud Storage. You can also set up a repeating schedule for transferring data, as well as transfer data within Cloud Storage, from one bucket to another.