Sqoop Command

-

Compress command:

To decrease the size of data after importing to HDFS, we can use the option –compress while executing the command.

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table categories --hive-import --create-hive-table --hive-table vanshu.sqoop_retailer -m1 --compress;

 

direct command:

Sqoop can handle bulk transfers very well. We can speed up the transfers by using the –direct parameter. Sqoop can handle bulk transfers very well. You can speed up the transfers by using the –direct parameter.

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table categories --direct --target-dir /user/cloudera/Retailer-sqoop--split-by category_id-m1;

 

 

Incremental Append in Sqoop:

1)Insert  new values in tables present in Mysql:

INSERT INTO categories VALUES(60,9,"Cricket12");

INSERT INTO categories VALUES(61,9,"Volley ball");

INSERT INTO categories VALUES(62,8,"Basket ball");

 

2) Sqoop import incremental append command:

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table categories --target-dir /user/cloudera/sqoop-incre1 -m1--incremental append--check-column category_id--last-value 59;

 

 

Check-column: used as reference column

59: last value which is present in table before inserting new values.

Retrivesparticular columns.

 

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table categories --columns "category_name, category_department_id" --split-by category_id --target-dir /user/cloudera/retailer-sqoop -m1

 

Incremental Last modified:

·        Works with records which are having time stamp.

·        We need to mention the last time stamp which is present other than which has been inserted newly.

1)Insert values into table orders.

insert into orders values(68884,"2018-05-30 00:00:04",5584,"ON_HOLD");

 

2)Syntax:

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table orders --target-dir /user/cloudera/sqoop-inc123 -m1 --incremental lastmodified --check-column order_date --last-value "2014-07-23 00:00:00";

 

Sqoop-HBase Integration:

INTERNAL MYSQL

·       Create a table in Hbase:

·       Sqoop import command for importing data into hbase table:

sqoop import --connect jdbc:mysql://quickstart.cloudera:3306/gusto --username root --password cloudera--table visit123 --hbase-table visits --column-family v_details--hbase-row-key name -m 1;

Hbase-row-key name: row key  from table

v_details: column family from Hbase

visit123: table in mysql

·       Verify in Hbase :

scan ‘visits’;

 

EXTERNAL MYSQL:

·       Create a table in Hbase:

Create ‘data’,’data_details’

·       Sqoop import command for importing data into hbase table:

sqoop import --connect jdbc:mysql://192.168.56.1/vanshu --username root --password root --table data1--hbase-table data --column-familydata_details --hbase-row-key sno -m 1

 

·       Verify in Hbase :

Scan ‘data’

 


Comments

Post a Comment

Popular posts from this blog

Hadoop

-
Image
“90% of the world’s data was generated in the last few years.” Due to the advent of new technologies, devices, and communication means like social networking sites, the amount of data produced by mankind is growing rapidly every year. The amount of data produced by us from the beginning of time till 2003 was 5 billion gigabytes. If you pile up the data in the form of disks it may fill an entire football field. The same amount was created in every two days in  2011 , and in every ten minutes in  2013 . This rate is still growing enormously. Though all this information produced is meaningful and can be useful when processed, it is being neglected. What is Big Data? Big data  is a collection of large datasets that cannot be processed using traditional computing techniques. It is not a single technique or a tool, rather it has become a complete subject, which involves various tools, technqiues and frameworks. What Comes Under Big Data? Big data involves the data p...
Read more

Problem Statement Of Real Estate Use Cases

-
Hive Use Case – Real Estate Analysis   Due to an industry, real estate activity is outlined as any economic dealings associated with the acquisition, sale, owner-operation or lease of property. This in addition includes income-generating residential properties, like flat, buildings and single-room rentals. Real estate services are not enclosed within the sector. Also, samples of real estate services embrace brokerages, property management, appraisers, investment property analysts and different consultants. All analysts, working with Big Data are using Hive or some other tool to query dataset and get results with ease. Although other querying languages exists, Hive gives us a variety of new features when compared to traditional approaches. So,On demand of accelerating consumers in real estate field, filtered series of information was collected and handed over to data analyst team. Load data into Hive. Problem Statement:        ...
Read more

Problem Statement Of Bank Marketing analysis

-
Analyze Marketing data for call campaign by bank Try yourself! Your client- a Portuguese banking institution—ran a marketing campaign to convince potential customers to invest in bank term deposit. The data is related to direct marketing campaigns of the bank. The marketing campaigns were based on phone calls. Often, more than one contact by phone to the same client was required, in order to assess if the product (bank term deposit) would be ('yes') or not ('no') subscribed: Data Fields age;"job";"marital";"education";"default";"balance";"housing";"loan";"contact";"day";"month";"duration";"campaign";"pdays";"previous";"poutcome";"y" 1 - age (numeric) 2 - job : type of job (categorical: 'admin.','blue-collar','entrepreneur','housemaid','management','retired...
Read more