Use SQL Azure Database as a Hive Metastore

  • Article

When requesting a Hadoop cluster on http://www.hadooponazure.com/, select the option: Use SQL Azure for Hive Metastore to use a SQL Azure database as a Hive metastore.

 

When you select this option, you will need to specify the following parameters:

  • SQL Azure Server. You do not need to specify the fully qualified name (for example: servername.database.windows.net).

  • Target Database. Target database on the server in which Hive metastore will be created. If you haven't created a database for the Hive metastore, create one now. You can use either the Web edition or the Business edition. You pick the edition based on the number of tables that will reside in Hive datawarehouse. Note that Hive metastore only stores the metadata of the tables in Hive datawarehouse.

  • Username. The user must be a member of the following roles in the database you created earlier: db_ddladmin, db_datawriter, db_datareader. If you need to create a new user for this purpose, use the following instructions.

    1. Connect to master database using admin account and run the following query:

      create login hivedbuser with password='password'

    2. Connect to the new database and run the following query:

      create user loginuser2 from login hivedbuser

    3. Run the following query against the new database to add the user to appropriate roles.

      EXEC sp_addrolemember 'db_ddladmin', 'loginuser2'
EXEC sp_addrolemember 'db_datawriter', 'loginuser2'
EXEC sp_addrolemember 'db_datareader', 'loginuser2'

  • Password. Password for the user.

You also need to ensure that Windows Azure services have access to your SQL Azure server. To view the firewall rules for your database server, click Subscriptions\your subscription name>\server name> from the tree view in the left pane, and then click Firewall Rules button in the right pane. In the Firewall Rules for your server, verify that you see a rule name Microsoft.Services with IP Range Start as 0.0.0.0 and IP Range End as 0.0.0.0. If you don't see it, select the option Allow other Windows Azure services to access this server.