Learn Sqoop..!! (tutorial Day 8)

Being a part of Hadoop ecosystem, Scoop is an important interaction tool. When Big Data storages and analyzers such as MapReduce, Hive, HBase, Cassandra, Pig, etc. of the Hadoop ecosystem came into picture, they required a tool to interact with the relational database servers for importing and exporting the Big Data residing in them. Here, Sqoop provides feasible interaction between relational database server and Hadoop’s HDFS.

What is Scoop?

Sqoop is a open source tool designed to transfer data between Hadoop (Hive/HDFS or Hbase) and relational database servers. It is used to import data from structured Data stores or relational databases such as MySQL, Oracle to Hadoop related eco-systems like Hive or HDFS or HBase, and export from Hadoop file system back to relational databases, enterprise data warehouses. Sqoop only works with structured & relational databases such as Teradata, Netezza, Oracle, MySQL, Postgres etc.IF your DB doesn’t lie in this category, we can still use Scoop, using Extension framework – Connectors. You can find connectors online, and modify there code or write your own code using framework. Generally, JDBC connectivity comes handy with maximum of databases. so this resolves your issue.

Sqoop: “SQL to Hadoop and Hadoop to SQL”

Why is Sqoop used?

Scoop doesn’t have any server,so it is a client library. So it doesn’t matter you run it from Data node or from anywhere. It will find the instalation locally or  you can define the hadoop installation and then it will find the name node and run from there.

Sqoop uses MapReduce framework to import and export the data, which provides parallel mechanism as well as fault tolerance. Sqoop makes developers life easy by providing command line interface. Developers just need to provide basic information like source, destination and database authentication details in the sqoop command. Sqoop takes care of remaining part.

Sqoop provides many salient features like:

  1. Full Load
  2. Incremental Load
  3. Parallel import/export
  4. Import results of SQL query
  5. Connectors for all major RDBMS Databases
  6. Kerberos Security Integration
  7. Load data directly into Hive/Hbase

What are Connectors?

Scoop has connectors, which is a pluggable component that uses extension framework to enable scoop to import or export the data between Hadoop and Data stores. The most basic connector that ships with Sqoop is Generic JDBC Connector, and as the name suggests, it uses only the JDBC interface for accessing metadata and transferring dataAvailable connectors include Oracle, DB2, MYSql, PostgresSQL, Teradata, JDBC.

Scoop Architecture

 

How is Sqoop used?What all can we import/export ??

Scoop can be used to import/export :

  1. Entire table
  2. Part of table or just data using Where clause
  3. all tables of a Database

or we can use Scoop’s few commands like Eval (Evaluate), Options-File (convert your file command into Scoop commands) , all-Databases, all-tables and many more.

Scoop reads the table row by row into HDFS. The output of this Import table process is set of files containing copy of imported table. Since import is a parallel process, hence output will be many files.These files may be delimited text files, or binary Avro or sequence files.It tries to fetch metadata from db table & calculates the max and min values of Primary key of tables to identify the data range (Amount of Data). This value helps Scoop to divide the load between mappers. Generally it uses 4 mappers & no reducers.

Scoop is built on Map-reduce logic & uses JDBC API’s to create Java/class files to process this metadata and at end create a JAR  file of it. So once the import is complete you will see 3 files created. For example: Employee.java, employee.class and employee.jar

Let’s learn how to use Scoop to import tables. Lets assume you have a mysql database (RDBMS) and you are trying to import a Employee table from it into HDFS.

Command Syntax:

sqoop import –connect jdbc:mysql://localhost/databasename –username $USER_NAME –password $PASSWORD$ –table tablename –m 1

Example:

$ sqoop import –connect jdbc:mysql://localhost/scoop_db –username scp –password scp123 –table employee –m 1

Here we specify the:

  • database path (localhost)
  • database name (scoop_db)
  • connection protocol (jdbc:mysql:)
  • username (scp)
  • password  (There are many ways to provide the password like on command line, store it in a file and call it etc.)
  • Always use ‘- -‘ for all sub commands like CONNECT, USERNAME, PASSWORD
  • Use ‘-‘ for Generic commands like FILE

To verify the imported data in HDFS, use the following command

(syntax from internet).
$ $HADOOP_HOME/bin/hadoop fs -cat /employee/part-m-*

It will show you fields and data with comma separated.

Now lets see various syntax and examples:

  • Import an entire table:

sqoop import –connect jdbc:mysql://localhost/abc –table EMPLOYEE

  • Import a subset of the columns from a table:

sqoop import –connect jdbc:mysql://localhost/abc–table EMPLOYEES –columns   “employee_id,first_name,age,designation”

  • Import only the few records by specifying them with a WHERE clause

sqoop import –connect jdbc:mysql://localhost/abc –table EMPLOYEES  –where “designion=’ADVISOR’ “

  • If table has primary key defined, we can set Parallelism to command by explicitly set the number of mappers using --num-mappers. Sqoop evenly splits the primary key range of the source table, as mentioned above.

sqoop import –connect jdbc:mysql://localhost/abc –table EMPLOYEES –num-mappers 6

  • If there is not primary key defined in the table, the data import must be sequential. Specify a single mapper by using –num-mappers 1 or  give ‘-m 1′ option for import.Otherwise it gives error

 sqoop import –connect jdbc:mysql://localhost/db –username $USER_NAME –password $PASSWORD$ –table tablename –m 1

 

  • To try a sample query without importing data, use the eval option to print the results to the command prompt:

sqoop eval –connect jdbc:mysql://localhost/abc –query “SELECT * FROM employees LIMIT 10”

 

Follow for more…Read next article on What is Spark ?

Want to learn how HDFS works  …read here

Want to learn Hadoop Installation…click here !!

Advertisements

Don’t Die with Dieting….instead try DIET KITCHEN !!

Blogging is about giving more than it is about getting.

Diet Kitchen as the name suggests, serves the healthy and nutritious food, which is best of their kind. It’s an outlet in Gurgaon whose website display all the calorie and fat content, so the customer can carefully choose what they want to consume.

Generally, when we are hungry we crave for junk food like pizza, pasta, burgers, but never thought all this can be prepared and eaten in healthy way too!! They make nutritionally balanced meals, calculated to the core, which are just perfect for every person.

We recently got to order few food items from Diet Kitchen & everything was lip smacking.

Let me first say that food was delivered on time and was still hot (5/5). So in home deliveries this is first requirement which is superbly fulfilled by Diet Kitchen Team. Wraps, Pizza and Pasta were packaged and sealed nicely with no leakage. It was easy to handle and open. The packaging (5/5) impressed me as everything was nicely placed in the box, which had some paper napkins, sauces and disposable cutlery.

The food ordering process is straight forward and simple.  As a vegetarian i have become accustomed to a limited choice but this is certainly not the case with the Diet Kitchen. You can choose from their vast choice of sandwiches/burgers/pasta/pizza and many more meal options according to your own dietary requirements and preferences. They serve both Veg and Non veg options.

I shared my order with my parents. Generally, we don’t eat outside or ready meals, so i was skeptical about how good the food would taste, i feared they my parents will not like it, but was soooo happy when my father said I just loved the Pizza. Everything were delicious & lip smacking!!

We ordered the Potato Gainer Wrap, features a stuffed portion of Potatoes, green chilli, capsicum,onion ,sauces, wrapped with a hexagonal fold inside a flour tortilla and grilled. It was lip smacking & healthy too. There’s nothing new here in the way of ingredients but there’s some novelty in the combination. Next we tried Grilled Paneer Soya Veg pizza, which had no cheese and a very thin base crust. It was loaded with paneer, soya chunks & all gourmet exotic vegetables like bell pepper, onion, capsicum & other veggies. Cheese missing went unnoticed as it was scrumptious. Penne Mushroom Mix Sauce Pasta” served with 2 small brown breads. It was full of wheat pasta and mushrooms. It was too yummy & delicious. None of the ingredients got overwhelmed by the others.

collage

As per the quality and quantity of food (serves2), I think the prices are justified. This place is healthy destination for all food lovers, who can eat whatever they want in a healthy way. Highly Recommended!!

 

 

© Follow me on Instagram – creativeme1807
© Like us on Facebook – Ekta Sethi/Foodofy
© Follow me on Zomato – www.zomato.com/foodofy

© creativeme1807.wordpress.com

Find this restaurant at :

The Diet Kitchen Menu, Reviews, Photos, Location and Info - Zomato

BBQ Factory…grilling next !!

Bbq factory, a live grill or you can say a table grill concept restaurant is yet another on the list for buffet options. Its located on Golf course road. I visited this place for a Sunday lunch with few of my friends. Its medium window glass entrance gives an impression of small restaurant but don’t go with it. It has a huge capacity inside with a warm ambience. Seating is organized well & is very comfortable. They have live karaoke going on, and even visitors can go and sing there.

The menu is kind-of similar to the other barbeque restaurants. We were ushered to our table and bbq oven was set on the table. The freshly baked/veggies were set on oven which had Paneer tikka (4/5), Mushroom (3/5) and not to forget a new item on list was Tandoori Momos (3.5/5). We were served Salsa potato(5/5), Churrasco Pineapple(5/5), Crispy corns(5/5) on our plates. I personally loved the on plate delicacies served.

collage

In drinks we tried Pushpa Behosh (4/5): Mild cocktail and very refreshing, Pomegranate Mojito (5/5): Mix of lime, pomegranate, mint making it summer chiller. Another one was detox (4/5) which was quite refreshing and was full of flavour.

Now talking about the mains. They have a lot of Salads variety placed .We tried Daal Makhani with paneer malai & biryani.(5/5). Everything was lip smacking and delicious. Must recommend the biryani. Everything was full or superb aroma.

In desserts you have got many options, but personally I loved Jalebis , apple pie and choco mud pudding with strawberry icecream (5/5).

The staff was very friendly and welcoming. Even the chef was so much involved that he was coming to our desk and asking feedback on dishes served.

This place is worth visiting& must try for corporate parties.

 

Like us on Facebook – Ekta Sethi/Foodofy

Follow me on Instagram – creativeme1807
Follow me on Zomato – Foodofy

 

Find it on:
BBQ Factory Menu, Reviews, Photos, Location and Info - Zomato

Buéno café…Premium bakery & Sandwich café !!

Buéno café is located in MGF Metropolis mall, Gurgaon. It is a healthy café that serves some delicious desserts & healthy sandwiches. Along with it, the best thing i loved is their presentation. Food was delivered in white packages stating…..Premium bakery & Sandwich café !!

This place is a newly opened delivery outlet & serves the best of their kind. We recently got to order few food items from Bueno & everything was lip smacking.
Packaging: Loved the packing. Most of it was packed in little jars with their labels. Sandwiches were packaged and sealed nicely with no leakage. It was easy to handle and had veg/ non veg mentioned properly on them. The cookies , Hummus ,Cheese cake and Banoffee were packed in an airtight glass bottle with Lid and was vacuum packed.

Food: We ordered the Assorted Pita chips” target=”_blank”>Pita chips with Hummus. They were colorful and square shaped baked chips. The flavor combination with Hummus sounded so good. The chip has a hearty crunch and really work well with all sorts of dips. Then we tried their variety of cookies like Double Chocolate chip Cookie & Tender Coconut cookies: These were scrumptious crispy mini cookies filled with chocolate chips. They were nicely packed in glass jars. They added to best of the appetizer’s.

collage1

Next we had the Veg club Sandwich ,which had multi grain bread well stuffed with veggies(onion, tomatoes),mustard and slices of the mozzarella cheese. The best part was the bread was very soft and was not overstuffed.

In desserts we had Banoffee & Blueberry Cheese cake. It was worth binging for. I enjoyed every scoop of it and finished till the end. After all it was my fav and tasted yummy . It s must recommendation.
Delivery time: Food was delivered on time .The Server was very humble & polite.

Prices: Little over priced but worth every bit, as they maintain good quality food.

Follow me on Zomato

Find it on :
buéno Menu, Reviews, Photos, Location and Info - Zomato

How to Remove Null Value from Dashboard prompts in OBIEE

Currently i am facing a weird issue in Dashboard prompts of OBIEE 12c.There are invalid Null values that are not present in database but showing in prompt columns in OBIEE. For some of the columns Null Values is valid but for other it is not. So Business wants to display null value in prompt only if it is valid and appears in DB column as well.

There are two ways to fix this issue,

a) edit this prompt and check the ‘Require user input’ property (doesnt work max times)

b) At RPD level , uncheck the Nullable option under the physical column as shown below:

obiee snippet

Then restart / recycle all the servers. Untill then this change wont get impacted. Do it for only necessary columns which will never have NULL value in DB also.

Follow for more on my FB page

or my OBIEE group

Samsung Galaxy C9 Pro..My best phone till date

So after a long time, i decided to buy a new phone.Lot of discussions and reading, debates went on with my husband, to understand which phone will suit me the best !! Lastly we landed up considering a choice between Samsung C9 Pro and a google phone.

To basically understand , what a girl wants in her phone, is best camera. especially the front one for better Selfie’s…Yes i am a selfie freak 😛  . Second priority is to maintain 2 sim cards, again an issue for my existing phone. Thirdly, a lot of space….as in 128gb or 64gb to accomodate my pics, selfie’s, movies, songs, messages , apps (insta, Ola, Jugnoo , whatsapp, skype and many more)..Fourthly, my priority was the phone should look sexy..and not some box type. Last but not the least, battery should be there for atleast a day.

Now after knowing all this specification, there is one and only 1 choice. SAMSUNG GALAXY C9 Pro. It was launched recently and was available on online booking only. I got my phone booked and when it was delivered…omg i just loved it.It was nicely packed.I placed an order for black color and it turned out to be very nice.

phone review

In the low cost segments, many companies have good products launched in the market. I choose Samsung for some obvious needs of mine like

  1. 64 GB space
  2. 6gb Ram
  3. dual Nano-SIMs trays , and one for a microSD card
  4. matte-finished metal back and slick glass front
  5. 16 megapixel front and rear camera’s, along with LED flash
  6. fingerprint sensor
  7. 6-inch screen with 6.9mm thickness, giving it a sleek look
  8. 4000mAh battery, and quick charging is supported with a compatible charger

One news which i am still waiting is an update on its android version. Still, i am using this phone for last 1 month, and i m loving it. It has no Heating issues, very sleek to hold, fast charging within 20 mins. Selfies i take have a diff class all over 🙂 

Cost: 36,900

Shop now:

Will highly recommend this phone to all my friends 🙂

Classroom…childhood memories cafe !!

Classroom, is recently opened in Sector 29, Gurgaon. As the name suggests its based on a school classroom theme. It has 3-4 floors and each of them is focused to show something different related to School. In the entry they have given Basketball setup outside with Classroom emblem also pictured on its glass walls. A notice board is set up to show imp notes.

Interiors are warm & give you a throwback of school memories. Basement is set up like a classroom with benches, full periodic table set up at the bar section. Other floors have graffiti of school kids carrying the bags, Flames set to Love between every relation, old school taglines. You have a canteen set up made here names “Ramu kaka ki canteen”. Last but not the least, even in washrooms they have set like schools. Some or the other taglines written on the doors. Everything is done in a very unique and perfect way to give us a memorable time spending here. Lights are neither too dark, as we see in other pubs. Also they have given a play arena, where Pool table /Fuss ball and other things are set up. Just loved this place.

colgg1

The menu (5/5) is quite diverse for both veg and non veg. They have given menu some very unique names like snack page is named as “Picnic Munches”, then next you have is First Break items, then Lunch break items and so on….They serve pan Asian delicacies like North Indian, Italian, Parsi. They have given very Hatt-Ke names to their dishes also, which is very impressive & suits the theme of the restaurant. Like few drink names are “Assembly”, “May I come in Madam” etc.

I visited here for a Wednesday dinner with friends. We started with the veg cheesy kebabs which were crunchy in every bite to binge on it. Next we ordered Paneer 65, which tasted commendable. Too scrumptious to resist.For Non veg the staff suggested we should try their signature dishes like, “CMC“, which is the classroom Maska chicken & Marghi-na-Farcha (Parsi Delicacy). The chicken was well roasted with each flavor standing out & very soft to even melt in mouth. Both are a must try. Not to forget the chutneys were awesome. We asked our server to fill the dips so many times 😛 All the servings were lip smacking .

Along with the food, we tried few of their Signature cocktails / Mocktails (5/5),

  • Sweet like you: Delicious strawberry milk with guava juice. Perfectly done.
  • Couple Chemistry: Vodka and green apple. The amalgamation of both is commendable
  • Assembly: I loved this mocktail. It had carribean cream with cranberry & pineapple flavor.
  • May I come in Please: It was infusion of Sambuca with vodka & guava juice. Finished with Hazelnut foam. Highly recommendable.

We are so full, that no chance to try anything else. But yes nothing was a miss and tasted yummy. Everything was nicely presented & was delectable.  We will definitely visit again for trying rest of their menu items.

collg2

Portions: (5/5) They provide good quantity portions, so you don’t have to worry for it. Each item ordered can serve 2. Presentation: (4/5): Food was nicely presented with all fresh dips. Service: (5/5) Service was really good and servers even knew a lot about the dish. They were even suggesting us to choose dishes as per our requirements.

Prices:  INR 1200 for two {approx.}

 What the ratings stand for: 5 = Excellent, 4 = Very Good, 3 = Good, 2 = Fair, 1 = Disaster.

Coverage by : Ekta Sethi (Follow me on Zomato

Find this place on Zomato:

The Classroom Menu, Reviews, Photos, Location and Info - Zomato

%d bloggers like this: