Hive view is a web GUI that lets you easily interact with HIVE, which comes pre-deployed in the Ambari managed cluster.
Ambari 2.5.0 comes with the new version of Hive view (2.0.0). The hive view 2.0 supports a lot of new features, one of them is Table Manager. Before Ambari 2.5.0, managing and creating table had to done through query tab, by writing queries. Table manager in Hive 2.0.0 provides a GUI wizard to create a table definition, or create a table, alter a table and load data by uploading the file of format CSV, JSON or XML.
This article is an introduction to Table manager in Hive View 2.0
Creating a new Hive View 2.0 Instance
Ambari comes with auto created instances of Hive view. If you wish to create a new instance of hive view, follow the steps:
- Click Manage Ambari to open the Ambari Administration user interface.
- Click Views, expand the Hive View, and click Create Instance.
- On the Create Instance page, select the Version as 2.0.0.
Table Manager Wizard
To create a table using Table manager wizard, open a Hive view 2.0 instance, navigate to Tables tab
Create a managed table
- Create a new table by, clicking on the ‘+’ icon, which opens the create table wizard. Enter table name, column name and choose a data type from the dropdown
2. If you wish to create a partitioned column or clustered column, click on the gear icon against the column
3. Create table allows to set advance hive properties mentioned below:
- Transactional : Turn on transaction support in Hive, by checking this flag. Note that the table must be bucketed and stored using an ACID compliant format (such as ORC).
- Location : Hive stores the table data for managed tables in the Hive warehouse directory in HDFS which is configured in hive-site.xml with property hive.metastore.warehouse.dir. The default location is /apps/hive/warehouse. The location can be changed using the Location text field.
- File Format : The default file format for CREATE TABLE statement is ORC. choose a format from the file format dropdown.
- Row Format : Select a row format such as Field terminator, Lines terminator, and Stored File type.
4. Set Additional Table properties as key value pairs :
After setting all the required properties, click on create table button. Once the table is created, tables tab view shows the table details and storage information.
Create table by uploading a file
If you have a data file of the format CSV,JSON or XML, Upload table feature allows to creates a table from the data file. To upload and create a table, click on Upload Table on create table wizard
- Choose file type and format according to the file being uploaded.
- File can be uploaded from HDFS or local machine accessing the view
- If the first row of the CSV is a header row, check the ‘Is first row header?’ Checkbox.
- File name would be set as table name, which can be changed.
- Create a partitioned column or clustered column, using the gear icon against the column.
- Advance properties and table properties can be set as explained before.
Note:
- Once a table is created by uploading a file, uploading more files to the same table is not supported.
- The json files should follow the format given below:
[{
"col1Name": "value-1-1",
"col2Name": "value-1-2"
}, {
"col1Name": "value-2-1",
"col2Name": "value-2-2"
}]
Alter Table
Once a table is created, table can be altered to add new columns or change the column name or column datatype. You can also rename the table or delete the table.
Note:
- Alter table does not allow altering a clustered or partitioned column.
- It does not allow adding a new column that is clustered or partitioned.
- Also, removing an existing table property is not allowed
