Hive+HDFS external schema

Step 1: Prepare test data

If you already have Hive data, you can skip this step.

1. Activate E-MapReduce. The VPC where MaxCompute and the Hadoop cluster are located must be in the same region.

2. Prepare Hive test data

   1. Log on to the E-MapReduce console and select a region in the upper-left corner.

   2. On the My Clusters page, click Create Cluster.

      1. Software Configuration

         In the top navigation bar, select a resource group. By default, all resources of your account are displayed. Configure other parameters as described in the following table.

         Parameter	Description
         Region	The cluster is created in the selected region. The region cannot be changed after the cluster is created. The MaxCompute project and the VPC that hosts the Hadoop cluster must be in the same region.
         Business Scenario	In this example, select Custom Cluster.
         Product Version	Select a version based on your requirements. We recommend that you use the latest version.
         High Service Availability	This feature is disabled by default. If you enable High Service Availability, E-MapReduce distributes master nodes across different underlying hardware to reduce the risk of failures.
         Optional Services (Select One At Least)	In this example, select HADOOP-COMMON(3.2.1), HDFS(3.2.1), HIVE(3.1.3), and YARN(3.2.1).
         Collect Service Operational Logs	Enabled by default. if you want to collect service operational logs, you can modify the collection status on the Basic Information tab of the cluster details page.
         Metadata	In this example, use a Self-managed RDS instance. For more information about how to create an RDS instance, see Create an ApsaraDB RDS for MySQL instance and configure a database. For an EMR cluster that is used in the production environment, we recommend that you use independent ApsaraDB RDS for MySQL. For example, you can use ApsaraDB RDS for MySQL 5.7 of Alibaba Cloud high-availability edition. Enter the RDS connection information in hivemetastore-site.xml: javax.jdo.option.ConnectionURL The JDBC URL for the metastore connection. Example: jdbc:mysql://rds.host.name/hive_db_name. javax.jdo.option.ConnectionUserName The username of the metastore database account. javax.jdo.option.ConnectionPassword The password of the metastore database account.

         These configurations are for this example only. For more information about how to create a DataLake cluster in the E-MapReduce console, see Create a DataLake cluster.

      2. Hardware Configuration

         Parameter	Description
         Billing Method	Subscription Pay-as-you-go For testing, use the Pay-as-you-go billing method. After successful testing, you can release the cluster and create a new Subscription cluster for production.
         Zone	The zone cannot be changed after the cluster is created. Select the zone carefully.
         VPC	Select a VPC in the specified region. If none is available, click Create VPC. After the VPC is created, click Refresh to select the newly created VPC.
         vSwitch	Select a vSwitch in the specified zone of the VPC. If no vSwitch is available in the zone, you must create one.
         Default Security Group	Important Enterprise security groups created in the ECS console are not supported. If you have an existing security group, you can select it. Otherwise, create a new one.
         Node Group	Configure the master node group, core node group, or task node group based on your business requirements. For more information, see Configure hardware and networking. In this example, enable Assign Public Network IP for the master node group. You can use the default values for other parameters.
         Cluster Scaling	Configure auto scaling rules.

      3. Basic Configuration

         Parameter	Description
         Cluster Name	The name of the cluster. The name must be 1 to 64 characters in length and can contain Chinese characters, letters, digits, hyphens (-), and underscores (_).
         Identity Credentials	Key Pair: To use password-free authentication, select Key Pair. For more information, see Manage SSH key pairs. Password: The password for remotely logging on to the master node of the cluster. Record this password for future use.

   3. Log on to the master node of the cluster and use Hive to prepare data.

      1. Log on to the master node of the cluster

         1. Log on to the E-MapReduce console and select a region in the upper-left corner.

         2. Click Nodes for the target cluster.

         3. On the Nodes tab, click the icon in the node group's row, and then click a node ID to open the Instance details page.

         4. On the Instance page for the node ID, click Connect.

         5. In the Remote connection dialog box, use the default Workbench, and click Sign in now.

         6. In the logon window, enter the required instance information.

            Note

            When you create a key pair, the private key is automatically downloaded and saved as a .pem file. You can select SSH key authentication and upload the private key file for password-free authentication.

      2. Prepare Hive test data. After you log on to the master node of the cluster using Workbench, run the following commands in the terminal:

         [user@emr-node ~]$ hive
         
         -- Create a database.
         hive> CREATE database IF NOT EXISTS myhive;
         
         -- Switch to the database.
         hive> USE myhive;
         
         -- Create a non-partitioned table.
         hive> CREATE TABLE IF NOT EXISTS employees (
                 id INT,
                 name STRING,
                 age INT,
                 department STRING
               )
               ROW FORMAT DELIMITED
               FIELDS TERMINATED BY ','
               STORED AS TEXTFILE;
         
         -- Insert data.
         hive> INSERT INTO employees VALUES(1, 'John', 25, 'Sales'),
             (2, 'Jane', 30, 'Marketing'),
             (3, 'Mike', 35, 'Engineering'),
             (4, 'Sarah', 28, 'HR'),
             (5, 'David', 32, 'Finance'),
             (6, 'Linda', 29, 'IT'),
             (7, 'Robert', 31, 'Operations'),
             (8, 'Emily', 27, 'Research'),
             (9, 'Michael', 33, 'Development'),
             (10, 'Chris', 26, 'Support');
         
         -- Create a partitioned table.
         hive> CREATE TABLE employees_pt (
               id INT,
               name STRING,
               age INT
             )
           PARTITIONED BY (department STRING)
           ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
         
         -- Insert data.
         INSERT INTO employees_pt PARTITION (department='Sales') VALUES(1, 'John', 25),(2, 'Jane', 30),(3, 'Mike', 35);
         INSERT INTO employees_pt PARTITION (department='Marketing') VALUES(4, 'Sarah', 28),(5, 'David', 32);
         INSERT INTO employees_pt PARTITION (department='Engineering') VALUES(6, 'Linda', 29),(7, 'Robert', 31);
         INSERT INTO employees_pt PARTITION (department='HR') VALUES(8, 'Emily', 27),(9, 'Michael', 33),(10, 'Chris', 26);

         Query the non-partitioned and partitioned tables:

         -- Query the non-partitioned table.
         hive> SELECT * FROM employees;
         
         1       John    25      Sales
         2       Jane    30      Marketing
         3       Mike    35      Engineering
         4       Sarah   28      HR
         5       David   32      Finance
         6       Linda   29      IT
         7       Robert  31      Operations
         8       Emily   27      Research
         9       Michael 33      Development
         10      Chris   26      Support
         
         -- Query the partitioned table.
         hive> SELECT * FROM employees_pt;
         
         6       Linda   29      Engineering
         7       Robert  31      Engineering
         8       Emily   27      HR
         9       Michael 33      HR
         10      Chris   26      HR
         4       Sarah   28      Marketing
         5       David   32      Marketing
         1       John    25      Sales
         2       Jane    30      Sales
         3       Mike    35      Sales

Step 2: Create a Hive+HDFS external data source

1. Log in to the MaxCompute console and select a region in the upper-left corner.

2. In the left-side navigation pane, choose Manage Configurations > External Data Source .

3. On the External Data Source page, click Create External Data Source.

4. In the Create External Data Source dialog box, configure the parameters. The following tables describe the parameters.

   Parameter	Required	Description
   External Data Source Type	Required	Select Hive+HDFS.
   External Data Source Name	Required	Enter a custom name. Naming conventions: Must start with a letter and can contain only lowercase letters, underscores, and digits. Cannot exceed 128 characters. For example, hive_hdfs.
   Description	Optional	Enter a description as needed.
   Network Connection Object	Required	The name of the network connection. Select or create a network connection from MaxCompute to the VPC network of the Alibaba Cloud E-MapReduce or Hadoop cluster. Important The VPC must be in the same region as the MaxCompute external data source and the project to which the external data source is attached. For more information about the parameters, see the Create a network connection between MaxCompute and the target VPC network step in Network connection process.
   Cluster Name		The name that represents the NameNode in a high-availability Hadoop cluster. To obtain the cluster name of an EMR cluster, perform the following steps: Log on to the E-MapReduce console and select a region in the upper-left corner. Click the target cluster's ID to open the cluster details page. On the Services tab, click Configure for the HDFS service to go to the Configure page. Switch to the hdfs-site.xml tab and search for dfs.nameservices in the By Name field. The value of this configuration item is the Cluster Name.
   NameNode Address		The endpoints and port numbers of the active and standby NameNode services of the destination Hadoop cluster. The port number is usually 8020. To obtain the NameNode address of an EMR cluster, perform the following steps: Log on to the E-MapReduce console and select a region in the upper-left corner. Click the target cluster's ID to open the cluster details page. On the Services tab, click Status for the HDFS service to go to the Status page. In the Components section, click the icon next to NameNode to expand the topology list. Obtain the master-1-1 node's Private IP Address. The NameNode Address format is Internal IP:8020.
   HMS Service Address		The endpoints and port numbers of the Hive metadata services of the active and standby NameNodes in the destination Hadoop cluster. The port number is usually 9083. Log on to the E-MapReduce console and select a region in the upper-left corner. Click the target cluster's ID to open the cluster details page. On the Services tab, click Status for the Hive service to go to the Status page. In the Components section, click the icon next to HiveRuntime to expand the topology list. Obtain the Private IP Address of the master-1-1 node. The HMS Service Address is in the format Internal IP:9083.
   Fs.Defaultfs
   Authentication Type		Only No authentication is supported.
   Create vSwitch		MaxCompute accesses the data source over a VPC. By default, the reverse access 2.0 solution is used. This solution requires you to configure a vSwitch in a specific zone to establish the metadata access link. The zones where vSwitches are available in each region are described in the UI prompts. In the VPC where the data source resides, select an existing vSwitch or create a new one that meets the zone requirements.
   Foreign Server Supplemental Properties	Optional	Additional properties for the external data source. After you specify these properties, tasks that use this external data source can access the source system based on the defined behavior. Note For information about the supported parameters, see subsequent updates to the official documentation. The specific parameters will be gradually made available as the product evolves.

5. Click OK to create the external data source.

Step 3: Create an external schema

1. Operations on external schemas must be performed in a MaxCompute project. Therefore, you must enable the schema-level feature for the project.

   1. In the left-side navigation pane, choose Manage Configurations > Projects.

   2. On the Projects page, click Enable Schema in the Actions column for the target project.

2. Before you perform schema operations or query data from an external schema, you must enable the schema feature for the SQL syntax. To do this, add the SET odps.namespace.schema=true; statement before your SQL statement.

3. In a MaxCompute client, run the following command to create an external schema that references a Hive+HDFS external data source:

   CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema_name>
   WITH  <hive_foreign_server_name>
   ON '<database_name>' ;

   The following table describes the parameters.

   • external_schema_name: The custom name of the external schema. In this example, the name is test_schema_hive.

   • hive_foreign_server_name: The name of the external data source that you created. In this example, the name is hive_hdfs.

   • database_name: The name of the Hive database. In this example, the name is myhive.

Step 4: Use SQL to access the data source

• List the names of Hive tables

  • Method 1:

    SHOW tables IN <external_schema_name>; --external_schema_name is the name of the external schema.

  • Method 2:

    USE SCHEMA <external_schema_name>; --external_schema_name is the name of the external schema.
    SHOW tables;

• Examples

  • Query the names of all tables in the external schema named test_schema.

    USE SCHEMA test_schema;
    SHOW TABLES;

    Sample result:

    ALIYUN$xxx@test.aliyunid.com:employees_pt
    ALIYUN$xxx@test.aliyunid.com:employees

  • Query data from the employees table in the test_schema schema of the test project.

    SELECT * FROM test.test_schema.employees;

    Sample result:

    +------------+------------+------------+------------+
    | id         | name       | age        | department | 
    +------------+------------+------------+------------+
    | 1          | John       | 25         | Sales      | 
    | 2          | Jane       | 30         | Marketing  | 
    | 3          | Mike       | 35         | Engineering | 
    | 4          | Sarah      | 28         | HR         | 
    | 5          | David      | 32         | Finance    | 
    | 6          | Linda      | 29         | IT         | 
    | 7          | Robert     | 31         | Operations | 
    | 8          | Emily      | 27         | Research   | 
    | 9          | Michael    | 33         | Development | 
    | 10         | Chris      | 26         | Support    | 
    +------------+------------+------------+------------+

  • Copy data from the federated external table employees to the data warehouse.

    MaxCompute does not allow you to run CREATE TABLE AS operations on an external schema. Therefore, you must run the USE SCHEMA Default; statement to switch to the default schema before you run the subsequent statements:

    -- Copy data from the federated external table to the data warehouse.
    CREATE TABLE employees_copy AS SELECT * FROM test.test_schema.employees;
    
    -- Query the copied table data in the data warehouse.
    SELECT * FROM employees_copy;

    Sample result:

    +------------+------------+------------+------------+
    | id         | name       | age        | department | 
    +------------+------------+------------+------------+
    | 1          | John       | 25         | Sales      | 
    | 2          | Jane       | 30         | Marketing  | 
    | 3          | Mike       | 35         | Engineering | 
    | 4          | Sarah      | 28         | HR         | 
    | 5          | David      | 32         | Finance    | 
    | 6          | Linda      | 29         | IT         | 
    | 7          | Robert     | 31         | Operations | 
    | 8          | Emily      | 27         | Research   | 
    | 9          | Michael    | 33         | Development | 
    | 10         | Chris      | 26         | Support    | 
    +------------+------------+------------+------------+