Sqoop Hadoop Tutorial | What is Sqoop in Hadoop | Sqoop Tutorial For Beginners | Simplilearn Video Lecture | Taming the Big Data with HAdoop and MapReduce - Software Development

[Music]
scoop is an Apache Hadoop ecosystem
project it is a command-line interface
application for transferring data
between relational databases and Hadoop
it supports incremental loads of a
single table or a free-form sequel query
imports can also be used to populate
tables in hive or HBase exports can be
used to put data from Hadoop into a
relational database while companies
across industries are trying to move
from structured relational databases
like MySQL Teradata Netezza
and so on to Hadoop there were concerns
about the ease of transitioning existing
databases it was challenging to load
bulk data into Hadoop or access it from
MapReduce users had to consider data
consistency production system resource
consumption and data preparation data
transfers using script was both time
consuming and inefficient direct access
of data from external systems was also
complicated this was resolved with the
introduction of scoop scoop allows
smooth import and export of data from
structured databases along with Uzis
scoop helps in scheduling and automating
import and export tasks scoop in real
life can be used in online marketers
coupon comm uses scoop to exchange data
between Hadoop and IBM Netezza data
warehouse appliance the organization can
query its structured databases and
transfer the results into Hadoop using
scoop the Apollo Group an education
company also uses scoop to extract data
from databases as well as to inject the
results from Hadoop jobs back into
relational databases scoop is a
Pachi Hadoop ecosystem project its
responsibility is to import or export
operations across relational databases
like MySQL MS sequel and Oracle to HDFS
let's discuss the various reasons for
using scoop sequel servers are deployed
worldwide a sequel server is the primary
way to accept the data from a user
nightly processing is being done on
sequel servers for years
scoop allows you to move data from
traditional sequel DB to Hadoop HDFS as
Hadoop makes its way into enterprises
transferring the data using automated
scripts is inefficient and
time-consuming
hence scoop is used traditional DB has
reporting data visualization and other
enterprise built-in applications however
to handle large data you need an
ecosystem the need to bring the process
data from Hadoop HDFS to the
applications like database engine or Web
Services is satisfied by scoop scoop is
required when the database is imported
from a relational database our db2
Hadoop or vice-versa a relational
database where our DB refers to any data
in a structured format databases in
minus QL or Oracle are examples of our
DB while exporting databases from a
relational database to Hadoop users must
consider consistency of data consumption
of production system resources and
preparation of data for provisioning
downstream pipeline while importing the
data base from Hadoop to a relational
database users must keep in mind that
directly accessing data residing on
external systems within a MapReduce
framework complicates applications it
also exposes the production system to
excessive loads originating from cluster
nodes
hence scoop is required in both
scenarios let's look at the benefits of
using scoop it transfers
from Hadoop to an R DB and vice versa it
transforms data in Hadoop with the help
of MapReduce or hive without extra
coding it is used to import data from an
r DB such as sequel MySQL or Oracle into
the Hadoop distributed file system or
HDFS it exports data back to the our DB
following is a summary of scoop
processing it runs in a Hadoop cluster
it imports data from the R DB or no
sequel DB to Hadoop it has access to the
Hadoop core which helps in using mappers
to slice the incoming data into
unstructured formats and place the data
in HDFS it exports data back into our DB
ensuring that the schema of the data in
the database is maintained here is an
outline of the process
scoop performs the execution in three
steps first the data set being
transferred is divided into partitions
next a map only job is launched with
individual mappers responsible for
transferring a slice of the data set
lastly each record of the data is
handled in a type safe manner as scoop
uses metadata to infer the datatypes
my sequel basics
this demo you will learn how to work
with my sequel databases type my sequel
- you training - P training to connect
to my sequel databases in this command
- you denote username which is training
and - P denotes password in this case
the password is also training type show
databases if you want to view all the
databases present in my sequel you can
see that there is a simply learned
database available which will be used in
the demo type use simply learn to enter
the simply learned database now as you
can see you are inside the database type
show tables if you want to view all the
tables present in the simply learned
database notice that there is an account
table present in the simply learned
database which will be used in further
demos of Scoop
this brings you to the end of this demo
in this demo you have learned to connect
to a my sequel database view the tables
inside a database and enter a database
use the commands shown on the image to
import data present in MySQL database
using scoop where simply learn is the
database name and device is the table
name the process of the scoop import is
summarized on the screen
scoop introspects the database to gather
the necessary metadata for the data
being imported a map only Hadoop job is
submitted to the cluster by scoop the
map only job performs data transfer
using the metadata captured in step 1
the imported data is saved in a
directory on HDFS based on the table
being imported users can specify any
alternative directory where the file
should be populated by default these
fields contain comma delimited fields
with new lines separating the different
records users can also override the
format in which data is copied by
explicitly specifying the field
separator and recording Terminator
characters further users can easily
import data in Avro data format by
specifying the option as Avro data file
with the import command scoop supports
different data formats for importing
data it also provides several options
for tuning the import operation let's
discuss the process of importing data to
hive and HBase first scoop takes care of
populating the hive meta store with
appropriate metadata for the table and
also invokes the necessary commands to
load the table or partition next using
hive import scoop converts the data from
the native data types in the external
data store into the corresponding types
within hive further scoop automatically
chooses the native delimiter set used by
hive if the data being imported has a
newline or other hive delimiter
characters in it scoop allows the
removal of such characters the data is
then correctly populated for consumption
in hive lastly after the import is
completed the user can operate on table
just like any other table in hive
now that you have seen the process in
importing data into hive let's talk
about the process involved in importing
data into HBase when data is imported
into HBase scoop can populate the data
in a particular column family in an
HBase table the HBase table and the
column family settings are required to
import a table to HBase data imported to
HBase is converted using its string
representation and insert it as utf-8
bytes use the commands shown on the
screen to import data to HBase connect
to the database using the first command
specify the parameters such as username
password and table name using the second
command create an HBase table with the
column family as specified in MySQL
using the third command now let's
discuss the process of exporting data
from Hadoop using scoop use the commands
shown in the terminal to export data
from Hadoop using scoop
listing table of my sequel DB through
scoop in this demo you have learned to
view all the commands through scoop and
list the table present in a database
through scoop type scoop help to view
all the supported commands in scoop
notice that there is a list tables API
that lists the available tables in a
database now let's see how you can do
that with a scoop command
type the command
to do that you will use the scoop list
Tables API - - Connect then the
string to connect to my sequel DB in
this demo we will use the simply learn
database
you can list the tables by providing the
database name which is simply learn - -
username which is training and - -
password which is training for the my
sequel DB
the result displayed on the screen
accounts
this brings you to the end of this demo
in this demo you have learned to view
all the commands through scoop you have
also learned to list the table present
in a database through scoop we need to
perform the following steps to export
data from Hadoop using scoop first
introspects the database for metadata
and transfer the data next transfer the
data from HDFS to the DB further scoop
divides the input data set into splits
it uses individual map tasks to push the
splits to the database each map task
performs this transfer over many
transactions to ensure optimal
throughput and minimal resource
utilization now we will look at the
various connectors using which we can
connect to scoop to different databases
the different types of scoop connectors
are generic JDBC default scoop and fast
path connectors the generic JDBC
connector can be used to connect to any
database that is accessible via JDBC the
default scoop connector is designed for
specific databases such as MySQL
PostgreSQL Oracle sequel server and db2
the fast path connector specializes in
using specific batch tools to transfer
data with high throughput for example
MySQL and Postgres equal databases we
have learned that scoop divides the task
into four default mappers let me explain
you how parallelism helps scoop in
dividing tasks other than default
mappers by default scoop typically
imports data using for parallel tasks
called
mappers increasing the number of tasks
might improve import speed but note that
each task adds load to your database
server you can influence the number of
tasks using the - M or num mappers
option but scoop views this only as a
hint and might not honor it in the
following screenshot we set parallelism
to eight common
commands are listed on the screen the
first command on the screen is to import
the data from the MySQL table scoop demo
to an HDFS directory note - m1 which
ensures that there is only one mapper
output in the second command note that
ID is greater than 2 which places a
condition on data to be imported you can
also specify a specific sequel query as
shown in the third command on the screen
where it says - II select start from
scoop underscore demo where ID equals 13
the third command on the screen shows an
export function please note the hyphens
and the double - before driver connect
username and password there are some
more commands listed on the screen for
your reference importing our DBMS table
to HDFS in this demo you will learn how
to import the table accounts from simply
learn DB to the default HDFS location
type HDFS DFS - RM - our accounts to
delete the accounts table from this
directory this will remove the accounts
table from the default directory if the
table exists
to command now in order to import the
accounts table from my sequel db2 HDFS
you will have to type the scoop import
connection string for my sequel DB you
will have to provide the simply learned
database username for my sequel password
for my sequel - - table and the name of
the table that you want to import to
HDFS
you
type HDFS DFS - cat accounts to confirm
the creation of this table
you
as you can see it shows that a directory
named accounts has been created
type HDFS DFS - LS accounts to view the
content of the accounts directory
you will notice that multiple part files
have been created you can view the files
by providing the whole file name
you
type HDFS DFS - cat accounts slash
asterisk
so let's view this
zero-zero file
you
notice the output displayed on-screen
which is the data present in
the accounts table this brings you to
the end of this demo you have now
learned to import a table to a default
location and to remove a table from the
default directory you have also learned
to create a table confirm its creation
and view the contents of the created
table this command will list all tables
in the simply learned database in MySQL
in this screen let's look at scoops
limitations client-side architecture
does impose some limitations in scoop
client must-have JDBC drivers installed
for connectivity with our DBMS
client-side architecture requires
connectivity to cluster from client
users have to specify username and
password it's difficult to integrate a
CLI within external application it is
not supported with no SQL DB because it
is tightly coupled with JDBC semantics
scooped - is the next generation version
of Hadoop following are the advanced
features when compared to scoop
client-server design addresses
limitations described earlier API
changes also simplify development of
other scooped connectors client requires
connectivity only to the scooped server
DB connections are configured on the
server by a system administrator and
users no longer need to possess database
credentials centralized audit trail
better resource management scoop server
is accessible via CLI REST API and web
UI scoop to is being actively developed
currently scooped to has fewer supported
features than scooped
Hey want to become an expert in Big Data
then subscribe to the simply learned
Channel and click here to watch more
such videos centered up and get
certified in Big Data click here
you