Manage Databases

In this chapter

This chapter explains various features of QueryIO related to databases.

• QueryIO Database
• Add Database
• Delete Database
• Database Migration
• Export Schema
• Configure Namespace
• Migration Statistics

QueryIO Database

In QueryIO, you can configure multiple databases to use Hadoop SQL module.

DB Connections view shows default databases:

• MetaStore Database : (MetaStore) To store HDFS metadata, Extended Metadata & user defined tags. Typical use case is for searching files on HDFS cluster based on metadata or user defined tags.
• AdHoc Analysis Database : (AnalyticsDB) To store processed data as a result of big data analysis. Typical use case is big data analytics, data summarization, ad-hoc queries & data warehousing.

Database Configuration

Displays connection details for selected database. Here, you can update the connection properties for selected database.

• JDBC Connection URL: Database connection URL of selected database.
• UserName: Database username
• Password: Database password.
• JDBC Driver Name: Driver class of selected database.
• JDBC Driver Jar: Jar containing JDBC drivers for selected database. No file is displayed for default "MetaStore" database.
• Maximum Connections: Maximum number of open connections allowed.
• Maximum Idle Connections: The maximum number of active connections that can remain idle in the pool without extra connections being released.
• Wait Time MilliSeconds: The length of time (in milliseconds) for a database connection to become available.

Database Schema

Schema of selected database is displayed here. All tables and their respective schema can be viewed. It also provide feature to delete or clear selected table.

Following details are displayed:

• Tables:
• Checkbox to select table.
• ID: Name of the table.
• Shows the schema of the selected table.
  • Table Selected: Name of the table whose details are displayed.
  • Column Name: Name of the table attribute.
  • Column Type: Data type of the corresponding attribute.

Add Database

If at any point of time you choose to use your custom database for Hadoop SQL module or if you want to use Hadoop SQL feature with new NameNode, then you would need to add a new database configuration.

Use following guidelines to add a new database to QueryIO

• Select Add action from the dropdown at the top of the Databases tab.
• New DB Connection Name: Enter name for the connection.
• Role: Select database mode as "Metadata/Tagging" or "Adhoc Analysis".
• Click on Save to create a new database configuration.



• At this point, you have created a new database configuration [Let's say: MyDBConfig]
• Fill in the required database connection details in Database Configuration: [Let's say: MyDBConfig]
• JDBC Connection URL: Connection URL for your database.
• UserName: Enter DB username.
• Password: Enter DB connection password.
• JDBC Driver Name: Driver class for your database.
• JDBC Driver Jar: Jar containing JDBC drivers for your database.
• Maximum Connections: Specify maximum number of open connections allowed.
• Maximum Idle Connections: Specify the maximum number of active connections that can remain idle in the pool without extra connections being released.
• Wait Time MilliSeconds: Specify the length of time (in milliseconds) for a database connection to become available.
• Click on Update to save the new database configuration. You need to provide connection details to successfully add database.

NOTE: Before a new database is added to QueryIO, it must be started externally.

Delete Database

This action is used to delete the selected database if it is not used by Hadoop SQL module.

Database Migration

Using database migration, you can migrate your existing database to a different database.

Use following guidelines to perform database migration.

• At this point, it is assumed that you have already saved the configuration details for your new database.
• From the DB Connections tab, select the database that you want to migrate: MetaStore(by default database).
• From the dropdown at the top of the DB Connections tab, select Migrate. You will see the following window.



• Select Destination Database from the Select Destination DB dropdown.
• Click on Migrate to migrate your database.

NOTE: Destination database can not contain tables that are already present in source database.

Export Schema

Select this action to export schema of selected database.

Click Download DDL Script to download the schema of database to local machine.

Configure Namespace

Use this option to change the namespace to be linked with selected database.

Click Change to select another namespace.

Migration Statistics

This section consists of statistical data about database migration processes. You can check the status database migrations.

Following migration details are displayed:

• Source Database: Database from which data will be copied
• Destination Database: Database in which all data will be transferred.
• Start Time: Starting time of migration process.
• End Time: Completion time of database migration.
• Status: Final result of migration process.
• Progress: Number of tables migrated out of total tables.
• Error: Any error occurred during migration process can be viewed by clicking on "View Error".

Copyright © 2018 QueryIO Corporation. All Rights Reserved.

QueryIO, "Big Data Intelligence" and the QueryIO Logo are trademarks of QueryIO Corporation. Apache, Hadoop and HDFS are trademarks of The Apache Software Foundation.