big data 5

 

PRACTICAL NO – 5

Aim: Implementing Database Operations on Hive.

 




To list out the databases in Hive warehouse, enter the command ‘show databases’.

The database creates in a default location of the Hive warehouse. In Cloudera, Hive database store in a /user/hive/warehouse.

The command to use the database is USE <data base name>

Copy the input data to HDFS from local by using the copy From Local command.




When we create a table in hive, it creates in the default location of the hive warehouse. – “/user/hive/warehouse”, after creation of the table we can move the data from HDFS to hive table.

The following command creates a table with in location of “/user/hive/warehouse/retail.db”

Note : retail.db is the database created in the Hive warehouse.

 

Data Manipulation Language (DML )

DML statements are used to retrieve, store, modify, delete, insert and update data in the database.

Example :

LOAD, INSERT Statements. Syntax :

LOAD data <LOCAL> inpath <file path> into table [tablename]

The Load operation is used to move the data into corresponding Hive table. If the

keyword local is specified, then in the load command will give the local file system path. If the keyword local is not specified we have to use the HDFS path of the file.

Here are some examples for the LOAD data LOCAL command

After loading the data into the Hive table we can apply the Data Manipulation Statements or aggregate functions retrieve the data.

Example to count number of records:

Count aggregate function is used count the total number of the records in a table.

‘create external’ Table :

The create external keyword is used to create a table and provides a location where the  table will create, so that Hive does not use a default location for this table. An EXTERNAL table points to any HDFS location for its storage, rather than default storage.

 



Insert Command:

The insert command is used to load the data Hive table. Inserts can be done to a table or a partition.

•  INSERT OVERWRITE is used to overwrite the existing data in the table or partition.

• 




INSERT INTO is used to append the data into existing data in a table. (Note: INSERT INTO syntax is work from the version 0.8)

 

Example for ‘Partitioned By’ and ‘Clustered By’ Command :



‘Partitioned by‘ is used to divided the table into the Partition and can be divided in to buckets by using the ‘Clustered By‘ command.

When we insert the data Hive throwing errors, the dynamic partition mode is strict  and dynamic partition not enabled (by Jeff at dresshead website). So we need to set the following parameters in Hive shell.

Comments

Post a Comment

Popular posts from this blog

ML programs

Image
1.Implement and demonstrate FIND-S alorithm for finding the mot spcific hypotheis based on a given set of training data samples. Read the training data from a .CSV file. import csv with open('C:/xampp/htdocs/girish/PlayTennis.csv', 'r') as f:     reader = csv.reader(f)     your_list = list(reader)     h = [['0', '0', '0', '0', '0', '0']]     for i in your_list:         print(i)         if i[-1] == "True":             j = 0             for x in i:                 if x != "True":                     if x != h[0][j] and h[0][j] == '0':                         h[0][j] = x                     elif x != h[0][j] and h[0] * [j] != '0':                         h[0][j] = '?'                     else:                         pass                     j = j + 1 print("Most specific hypothesis is") print(h) output: ['Outlook', 'Temperature', 'Humidity', 'W
Read more

big data 8

                                                                             PRACTICAL NO – 8 Aim: Implementing Clustering Algorithm Using Map-Reduce   Algorithm for Mapper Input: A set of objects X = {x1, x2… xn}, A Set ofinitial Centroids C = {c1, c2, ,ck} Output: An output list which contains pairs of (Ci, xj)where 1 ≤ i≤ n and 1 ≤j ≤ k Procedure M1←{x1, x2… xm} current_centroids←C Distance (p, q) =√Σd i=1 (pi– qi) 2 (where pi (or qi)is the coordinate of p (or q) in dimension i) for all xi ϵ M1 such that 1≤i≤m do bestCentroid←null minDist←∞ for all c ϵ current_centroids do       emit (bestCentroid, xi) i+=1   dist← distance (xi, c) if (bestCentroid = null || dist<minDist) then minDist←dist bestCentroid ← c end if end for     end for return Outputlist   Algorithm for Reducer Input: (Key, Value), where key = bestCentroid and Value =Objects assigned to the lpgr'; 1\] x centroid by the mapper Output:
Read more

big data 6

                                                          PRACTICAL NO – 6 Aim: Implementing Bloom Filter using Map-Reduce. 1.                  import java.io.DataOutputStream; 2.                  import java.io.FileOutputStream; 3.                  import java.io.IOException; 4.                  import org.apache.hadoop.util.bloom.BloomFilter; 5.                  import org.apache.hadoop.util.bloom.Key; 6.                  import org.apache.hadoop.util.hash.Hash; 7. 8.                  public class DepartmentBloomFilterTrainer { 9.                  public static int getBloomFilterOptimalSize (int numElements, float falsePosRate) { 10.         return (int) (-numElements * (float) Math.log(falsePosRate) / Math.pow(Math.log(2), 2)); 11. } 12.         public static int getOptimalK (float numElements, float vectorSize) { 13.         return (int) Math.round(vectorSize * Math.log(2) / numElements); 14. } 15.         public static void main (String[]
Read more