Can we create a table based on a view in Hive?
Yes, we can.!!
Let's create "View" by combining Emp and Dept tables.
-~ To create a view
CREATE VIEW Emp_View
AS
SELECT Ename, DName FROM Emp
INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo;
Now let's create the table based on the view.
CREATE TABLE EmpDept
AS
SELECT * FROM Emp_View;
In this article, you'll learn how to create a view and table based on a select statement.
-~ To create a view
CREATE VIEW Emp_View
AS
SELECT Ename, DName FROM Emp
INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo;
-~ To create a table
CREATE TABLE EmpDept
AS
SELECT Ename, DName FROM Emp
INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo;
Can we create a table based on a view? Click here.
DATABASE LEVEL:
Database or schema both are the same thing. These words can be used interchangeably.
The TBLPROPERTIES clause enables you to use your own metadata key/value pairs to tag the table definition.
There are also several predefined table properties, such as last-modified-user and last-modified-time, which Hive automatically adds and manages.
To view the properties of a table use the below command in hive prompt.
SHOW TBLPROPERTIES tblname;
This lists all the properties of the table.
If the table's input format is ORC (refer to the input file formats) then you'll see which compression (snappy or zlib) has opted. You'll see if the transactional property set to true or false. You'll also see the predefined table properties that managed by Hive.
This article aims to explain the usage of the SPLIT function in HiveQL. If you are looking for a similar function in SQL Server, then please click here.
Let's create a staging table to load the data temporarily.
CREATE TABLE tempData (col1 STRING);
Load the data to the table.
LOAD DATA INPATH 'Desktop/DataFile' OVERWRITE INTO TABLE tempData;
To split the data from the above-created temp table
SELECT word, count(1) AS count FROM
(SELECT explode(split(col1, '\s')) AS word FROM tempData) temp
GROUP BY word
ORDER BY word;
Split function splits the data based on the delimiter provided. The Explode function will further split the data into smaller chunks. Let's see what these explode and split functions are doing with another example.
Below is the patient's blood pressure variations information.
TableName: PatientsData
Systolic-Diastolic
122/80, 122/83, 130/83, 135/86, 140/95, 147/92
SELECT split(data,'\/') as split_data from PatientsData;
Result:
split_data
122,80
122,83
130,83
130,83
135,86
140,95
147,92
SELECT split(data,'\/')[0] AS Systolic, split(data,'\/')[1] AS Diastolic from PatientsData;
Result:
Systolic Diastolic
122 80
122 83
130 83
130 83
135 86
140 95
147 92
SELECT explode(split(data,'\/')) as exploded_data from PatientsData;
Result:
exploded_data
122
80
122
83
130
83
130
83
135
86
140
95
147
92
The purpose of this article is to address the different file formats and compression codecs in Apache Hive that are available for different data sets. We will also explore how to use them properly and when to use them.
HiveQL handles structured data only, much like SQL. In order to store the data in it, Hive has a derby database by default. The data will be stored as files in the backend framework while it shows the data in a structured format when it is retrieved. Some special file formats that Hive can handle are available, such as:
Commands are non-SQL statements such as setting a property or adding a resource. They can be used in HiveQL scripts or directly in the CLI or Beeline. The below commands are mostly used ones and are helpful while working with partitions, adding external jar files, and changing the configuration settings.
This article will explore some Hadoop basic commands that help in our day-to-day activities.
Hadoop file system shell commands are organized in a similar way to Unix/Linux environments. For people who work with Unix shell, it is easy to turn to Hadoop shell commands. Such commands communicate with HDFS and other Hadoop-supported file systems.
1) List-out the contents of the directory.
2) Create or delete a directory
To check the status of safemode
hadoop dfsadmin -safemode get
To change the safemode to ON
hadoop dfsadmin -safemode enter
To change the safemode to OFF / or to
leave the safemode
hadoop fs -put <sourcefilepath> <destinationfilepath>
Examples:
hadoop fs -put Desktop/Documents/emp.txt /user/cloudera/empdir
hadoop fs -copyFromLocal Desktop/Documents/emp.txt /user/cloudera/emp.txt
To know more about "copyFromLocal", "put" "copyToLocal" and "get", please click here.
4) Read the file
hadoop fs -cat /user/cloudera/emp.txt
The above command helps in reading the file however, one has to avoid using this command for large files since it can impact on I/O. This command is good for
files with small data.
5) Copy the file from HDFS to Local
System
This is reverse scenario of Put & CopyFromLocal. For more information click here.
6) Move the file from one HDFS location
to another (HDFS location)
Hadoop fs -mv emp.txt testDir
Hadoop fs -mv testDir tesDir2
Hadoop fs -mv testDir2/testDir /user/cloudera
7) Admin Commands
To view the config settings
go
to --> computer-browse folder-filesystem-->etc-->hadoop-->conf-->hdfs-site.xml
To
change the default configuration values such as dfs.replication or dfs.blocksize from hdfs-site.xml, use the sudo commands
Click "I" for insert option or to bring it in edit mode.
Modify the values as per your
requirement.
To save and exit :wq!
hadoop fs -tail [-f]
<file>
The Hadoop fs shell tail command shows the last 1KB of a file on console or stdout.
HDFS is a distributed file system designed to run on top of the local file system. Many times we may need to copy files from different sources i.e. from the internet, remote network, or from the local file system. There are "CopyFromLocal" and "Put" commands to help us in performing the task. While copying a file from the local file system to HDFS, if the file exists in the destination, the execution will fail and we will receive 'the file exists' error.
Let's assume the file "emp.txt" already exists in the path /user/cloudera.
Hadoop fs -put Desktop/emp.txt /user/cloudera/emp.txt
Hadoop fs -copyFromLocal Desktop/emp.txt /user/cloudera/emp.txt
Hadoop fs -copyFromLocal -f Desktop/Documents/emp.txt /user/cloudera/emp.txt
This is succeeded. The file is copied to the destination without any errors.
The usage of the "-f" option with -copyFromLocal will overwrite the destination if it already exists.
hadoop fs -copyFromLocal <Local system directory path> <HDFS file path>
A choice exists to overwrite an existing file using -f when using copyFromLocal. However, an error is returned if the file persists when "put" is executed.
In short, anything you do with copyFromLocal, you can do with "put", but not vice-versa.
CopyToLocal and Get:
These two commands are just opposite to "CopyFromLocal" and "Put".
The destination is restricted to a local file reference when we use copyToLocal. While using "Get" there are no such restrictions.
Anything you do with copyToLocal, you can do with "get" but not vice-versa.
hadoop fs -get <HDFS file path> <Local system directory path>
hadoop fs -copyToLocal <HDFS file path> <Local system directory path>
For complete HDFS commands please click here. For complete Hive DDL commands please click here.
Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...
