Difference between AWS glue and Hive warehouse

Apache Hive vs AWS Glue: What are the differences?

Apache Hive: Data Warehouse Software for Reading, Writing, and Managing Large Datasets. Hive facilitates reading, writing, and managing large datasets residing in distributed storage using SQL. Structure can be projected onto data already in storage; AWS Glue:Fully managed extract, transform, and load (ETL) service. A fully managed extract, transform, and load (ETL) service that makes it easy for customers to prepare and load their data for analytics.

Apache Hive and AWS Glue can be primarily classified as "Big Data" tools.

Some of the features offered by Apache Hive are:

• Built on top of Apache Hadoop
• Tools to enable easy access to data via SQL
• Support for extract/transform/load (ETL), reporting, and data analysis

AZURE BLOB STORAGE

• Service for storing large amount of unstructured object data, such as text or binary data
• Expose data publicly to the world, or store application data privately
• Common uses of blob storage
• Serving images or documents directly to a browser
• Storing files for distributed access
• Storing data for backup and restore, disaster, recovery and archiving

STORAGE ACCOUNT

• All access to storage is done through a storage account.
• This account can be a General purpose or a blob storage.

Spark Executors and Drivers

SPARK CONSISTS OF DRIVER PROGRAM

• Our code that defines which datasources to consume from and which data sources to consume to which is driving all our execution
• Responsible for getting the spark context
• It would understand how to communicate with the cluster manager.(Yarn mesos or stand-alone)

Loading facts in ETL

LOADING FACTS
The most important thing about loading fact tables is that first you need to load dimension tables and then according to the specification the fact tables.


The fact table is often located in the center of a star schema, surrounded by dimension tables. It has two types of columns: those containing facts and other containing foreign keys to dimension tables.

Installing spark on windows

 https://www.youtube.com/watch?v=2CvtwKTjI4Q&vl=en

1) Download specific version of spark
 http://spark.apache.org/downloads.html
2) Unzip and create a directory for spark

Wasb

What is HDFS?

The Hadoop Distributed File System (HDFS) is one of the core Hadoop components, it is how Hadoop manages data and storage. At a high level, when you load a file into Hadoop the "name node" uses HDFS to chunk the file into blocks and it spreads those blocks of data across the worker nodes within the cluster. Each chunk of data is stored on multiple nodes (assuming the replication factor is set to > 1) for higher availability. The name node knows where each chunk of data is stored and that information is used by the job manager to allocate tasks and resources appropriately across nodes.

SQL question challenge (Consecutive numbers)

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

SQL question challenge (Cancellation rates for trips)

SQL Schema

The Trips table holds all taxi trips.

TRIPS TABLE.
Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the

+----+----------------+-----------+--------------+--------------------+----------+

| Id | Client_Id      | Driver_Id | City_Id      |        Status         |Request_at|

+----+-----------+-----------+---------+--------------------+----------+

| 1  |     1          |    10     |    1         |     completed         |2013-10-01|

| 2  |     2          |    11     |    1         | cancelled_by_driver   |2013-10-01|

| 3  |     3          |    12     |    6         |     completed         |2013-10-01|

| 4  |     4          |    13     |    6         | cancelled_by_client   |2013-10-01|

| 5  |     1          |    10     |    1         |     completed         |2013-10-02|

| 6  |     2          |    11     |    6         |     completed         |2013-10-02|

| 7  |     3          |    12     |    6         |     completed         |2013-10-02|

| 8  |     2          |    12     |    12        |     completed         |2013-10-03|

| 9  |     3          |    10     |    12        |     completed         |2013-10-03|

| 10 |     4          |    13    |    12         | cancelled_by_driver   |2013-10-03|

+----+-----------+-----------+---------+--------------------+----------+

SQL question challenge (candidate winners)

SQL Schema
Table: Candidate

+-----+---------+
| id  | Name    |
+-----+---------+
| 1   | A       |
| 2   | B       |
| 3   | C       |
| 4   | D       |
| 5   | E       |
+-----+---------+
Table: Vote

SQL question challenge (Customer with no orders)

SQL Challenge
Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customer.

+----+-------+
| Id | Name  |   
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

DDL SCRIPTS

twittter location clustering based on tweets (Spark Mllib)

1)  Create a directory for twitter streams

 cd /usr/lib/spark 

 sudo mkdir tweets 

 cd tweetscd
 sudo mkdir data 

 sudo mkdir training

 sudo chmod  777 /usr/lib/spark/tweets/ 

These are the two folders which we would be using in this project
data :Would contain the master of the csv files which we would pretend coming from a training source.
training :  Source to train our machine learning algorithm

SSH to Hortonworks sandbox

1) Download the sandbox for hortonworks
2) Launch virtualbox
3) Once the sandbox is up and running, we would see a screen as shown below ( Has information about the localhost url and ssh servers)

HORTONWORKS SANDBOX

Movie ratings project part 3 (Analysis)

cont  from http://www.prathapkudupublog.com/2018/07/movie-ratings-project-part-2.html

link to github

***************************************************************

1) WHICH YEAR HAS THE MOST NO OF RATINGS
***************************************************************

select year(from_unixtime(rating_time)) rating_year,
       count(*) as cnt
from latest_ratings 
group by year(from_unixtime(rating_time))
order by rating_year DESC;
    
 YEAR    RATING_YEAR
 2018    1086549
 2017    1973721
 2016    2077152
 2015    1907576
 2014    584216
 2013    633573
 2012    792701
 2011    833774
 2010    982104
 2009    993111
 2008    1210356
 2007    1095710
 2006    1210803
 2005    1849719
 2004    1201656
 2003    1079130
 2002    910350
 2001    1239432
 2000    2033738
 1999    1231173
 1998    329704
 1997    763929
 1996    1733263
 1995    4

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.