Step 2: Preparations

Preparations Before Using DataArts Studio

If you use DataArts Studio for the first time, register a , buy a DataArts Studio instance, and create a workspace by following the instructions provided in Preparations. Then you can go to the created workspace and start using DataArts Studio.

In this example, the has all the permissions required for performing all the data operations on DataArts Studio so that the entire data governance process using DataArts Studio can be demonstrated.

Preparing a Data Source

This guide uses the collection of operations statistics from a taxi vendor in 2017 as an example. This example uses data from https://data.cityofnewyork.us/Transportation/2017-Yellow-Taxi-Trip-Data/biws-g3hs.

To facilitate demonstration, some sample data is provided to simulate source data. To integrate the source data into the cloud, you must store the sample data as a CSV file, upload the file to OBS, and use DataArts Studio DataArts Migration to migrate the data to other cloud services.

1. Create a CSV file (UTF-8 without BOM) named 2017_Yellow_Taxi_Trip_Data.csv, copy the sample data provided in the subsequent section to the CSV file, and save the file.
   To generate a CSV file in Windows, you can perform the following steps:

   1. Use a text editor (for example, Notepad) to create a .txt document and copy the sample data to the document. Then check the total number of rows and check whether the data of rows is correctly separated. (If the sample data is copied from a PDF document, the data in a single row will be wrapped if the data is too long. In this case, you must manually adjust the data to ensure that it is in a single row.)
   2. Choose File > Save as. In the displayed dialog box, set Save as type to All files (*.*), enter the file name with the .csv suffix for File name, and select the UTF-8 encoding format (without BOM) to save the file in CSV format.

2. Upload the CSV file to OBS.
   1. Log in to the management console and choose Storage > Object Storage Service to access the OBS console.
   2. Click Create Bucket and set parameters as prompted to create an OBS bucket named fast-demo.
      

      To ensure network connectivity, select the same region for OBS bucket as that for the DataArts Studio instance. If an enterprise project is required, select the enterprise project that is the same as that of the DataArts Studio instance.

      For details about how to create a bucket on the OBS console, see Creating a Bucket in Object Storage Service Console Operation Guide.

   3. Upload data to OBS bucket fast-demo.

      For details about how to upload a file on the OBS console, see Uploading a File in Object Storage Service Console Operation Guide.

The following shows partial sample data:

VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,RatecodeID,store_and_fwd_flag,PULocationID,DOLocationID,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount
2,02/14/2017 04:08:11 PM,02/14/2017 04:21:53 PM,1,0.91,1,N,237,163,2,9.5,1,0.5,0,0,0.3,11.3
2,02/14/2017 04:08:11 PM,02/14/2017 04:19:29 PM,2,1.03,1,N,237,229,1,8.5,1,0.5,2.06,0,0.3,12.36
1,02/14/2017 04:08:12 PM,02/14/2017 04:19:44 PM,1,1.6,1,N,186,163,2,9,1,0.5,0,0,0.3,10.8
1,02/14/2017 04:08:12 PM,02/14/2017 04:19:15 PM,1,1.2,1,N,48,48,2,8.5,1,0.5,0,0,0.3,10.3
2,02/14/2017 04:08:12 PM,02/14/2017 04:13:38 PM,5,0.61,1,N,161,162,1,5.5,1,0.5,2.19,0,0.3,9.49
2,02/14/2017 04:08:12 PM,02/14/2017 05:35:11 PM,1,19.31,2,N,152,132,1,52,4.5,0.5,12.57,5.54,0.3,75.41
1,02/14/2017 04:08:13 PM,02/14/2017 04:20:53 PM,1,1.9,1,N,236,143,1,10.5,1,0.5,1.85,0,0.3,14.15
2,02/14/2017 04:08:13 PM,02/14/2017 04:15:54 PM,1,0.61,1,N,48,164,1,6.5,1,0.5,1.66,0,0.3,9.96
2,02/14/2017 04:08:13 PM,02/14/2017 04:41:40 PM,1,6.04,1,N,244,262,1,25,1,0.5,6.7,0,0.3,33.5
2,02/14/2017 04:08:13 PM,02/14/2017 04:17:31 PM,1,1.39,1,N,170,234,1,8,1,0.5,1,0,0.3,10.8
2,02/14/2017 04:08:14 PM,02/14/2017 04:54:11 PM,2,10.12,1,N,140,189,1,37.5,1,0.5,7,0,0.3,46.3
2,02/14/2017 04:08:14 PM,02/14/2017 04:13:56 PM,1,0.71,1,N,179,7,2,5.5,1,0.5,0,0,0.3,7.3
2,02/14/2017 04:08:14 PM,02/14/2017 05:04:24 PM,1,18.1,2,N,263,132,1,52,4.5,0.5,15.71,5.54,0.3,78.55
2,02/14/2017 04:08:14 PM,02/14/2017 04:08:47 PM,1,0.02,1,N,231,231,2,2.5,1,0.5,0,0,0.3,4.3
2,02/14/2017 04:08:15 PM,02/14/2017 04:18:13 PM,1,1.34,1,N,100,162,1,8,1,0.5,1.2,0,0.3,11
1,02/14/2017 04:08:16 PM,02/14/2017 04:19:01 PM,1,1.8,1,N,239,151,1,9,1,0.5,2.15,0,0.3,12.95
2,02/14/2017 04:08:16 PM,02/14/2017 04:15:57 PM,1,1.06,1,N,68,170,1,6.5,1,0.5,1,0,0.3,9.3
2,02/14/2017 04:08:16 PM,02/14/2017 04:20:08 PM,2,1.5,1,N,161,142,1,9,1,0.5,2.16,0,0.3,12.96
2,02/14/2017 04:08:16 PM,02/14/2017 04:11:56 PM,1,0.62,1,N,87,88,2,4.5,1,0.5,0,0,0.3,6.3
2,02/14/2017 04:08:16 PM,02/14/2017 04:13:20 PM,1,0.88,1,N,262,236,2,5.5,1,0.5,0,0,0.3,7.3

Preparing a Data Lake

Before using DataArts Studio, you need to select cloud services or databases as the data lake foundation, which provides storage and compute capabilities. DataArts Studio provides one-stop data development, governance, and services based on the data lake foundation.

DataArts Studio can integrate cloud services such as DWS, DLI, and MRS Hive, as well as conventional databases such as MySQLOracle. For details, see Data Sources.

In this example, MapReduce Service (MRS) Hive is used as the data lake base of DataArts Studio. You need to create an MRS security cluster (with Kerberos authentication enabled) because DataArts Security cannot be interconnected with common MRS clusters. For details, see Buying a Cluster.

To ensure that the MRS cluster can communicate with the DataArts Studio instance, the MRS cluster must meet the following requirements:

• The MRS cluster must contain a Hive component.
• If you want to enable automatic generation of quality jobs based on the data standards in DataArts Studio DataArts Architecture, ensure that the MRS cluster version is 2.0.3 or later and that the cluster contains Hive and Spark components and at least four nodes. In this example, this function is required.
  If the connection fails after you select a cluster, check whether the MRS cluster can communicate with the CDM instance which functions as the agent. They can communicate with each other in the following scenarios:

  • If the CDM cluster in the DataArts Studio instance and the MRS cluster are in different regions, a public network or a dedicated connection is required. If the Internet is used for communication, ensure that an EIP has been bound to the CDM cluster, and the MRS cluster can access the Internet and the port has been enabled in the firewall rule.
  • If the CDM cluster in the DataArts Studio instance and the cloud service are in the same region, VPC, subnet, and security group, they can communicate with each other by default. If they are in the same VPC but in different subnets or security groups, you must configure routing rules and security group rules. For details about how to configure routing rules, see configuring routes. For details about how to configure security group rules, see configuring security group rules.
  • The MRS cluster and the DataArts Studio workspace belong to the same enterprise project. If they do not, you can modify the enterprise project of the workspace.
  

  During the creation of an MRS cluster, a security group is automatically created. You are advised to create an MRS security cluster first and then buy a DataArts Studio instance, selecting the same VPC and subnet as the MRS cluster and the security group (named in mrs_Cluster name_Random character format) that is automatically created. This ensures that the DataArts Studio instance can communicate with the MRS cluster by default.

  If you already have a DataArts Studio instance before creating an MRS cluster, you need to choose Access Control > Security Groups on the VPC console and add a rule to allow inbound traffic to the security group (named in mrs_Cluster name_Random character format) created by the MRS cluster. For details, see Configuring Security Group Rules.

Creating a Data Connection on Management Center

After the data lake is prepared, create a data connection on Management Center to connect to the cloud service that functions as the data lake.

1. On the DataArts Studio console, locate a workspace and click Management Center.
   Figure 1 Management Center
   

2. On the displayed Manage Data Connections page, click Create Data Connection.
   Figure 2 Creating a data connection
   

3. In the dialog box displayed, set data connection parameters and click OK.

   The following part describes how to create an MRS Hive connection. See Figure 3 for details.

   • Data connection type: MRS Hive
   • Name: Enter mrs_hive_link.
   • Tag: Enter a new tag name or select an existing tag from the drop-down list box. This parameter is optional.
   • Cluster Name: Select an existing MRS cluster.
   • Username: Enter the Kerberos authentication user. In an MRS policy, user admin is the default management user and cannot be used as the authentication user of the cluster that uses Kerberos authentication. Therefore, to create a connection for an MRS cluster that uses Kerberos authentication, perform the following operations:
     1. Log in to the MRS Manager as user admin.
     2. On FusionInsight Manager, choose System Settings and click Configure Password Policy to modify the password policy.
        • Set Password Validity Period (Days) to 0, indicating that the password never expires.
        • Set Password Expiration Notification (Days) to 0.
        • Retain the default values for other parameters.
     3. Choose System > Manage User. On the page displayed, add a dedicated user as the Kerberos authentication user. Select the user group superGroup for the user, and assign all roles to the user.
        

        • For clusters of MRS 2.x or earlier, the user must have permissions of the Manager_administrator or System_administrator role to create data connections in Management Center.
        • A user with only the Manager_tenant or Manager_auditor permission cannot create connections.
     4. Log in to MRS Manager as the new user and change the initial password. Otherwise, the connection fails to be created.
     5. Synchronize IAM users.
        1. Log in to the MRS console.
        2. Choose Clusters > Active Clusters, select a running cluster, and click its name to go to its details page.
        3. In the Basic Information area of the Dashboard page, click Synchronize on the right side of IAM User Sync to synchronize IAM users.
           

           • When the policy of the user group to which the IAM user belongs changes from MRS ReadOnlyAccess to MRS CommonOperations, MRS FullAccess, or MRS Administrator, wait for 5 minutes until the new policy takes effect after the synchronization is complete because the SSSD (System Security Services Daemon) cache of cluster nodes needs time to be updated. Then, submit a job. Otherwise, the job may fail to be submitted.
           • When the policy of the user group to which the IAM user belongs changes from MRS CommonOperations, MRS FullAccess, or MRS Administrator to MRS ReadOnlyAccess, wait for 5 minutes until the new policy takes effect after the synchronization is complete because the SSSD cache of cluster nodes needs time to be updated.
   • Password: Enter the password of the Kerberos authentication user.
   • KMS Key: Select a KMS key and use it to encrypt sensitive data. If no KMS key is available, click Access KMS to go to the KMS console and create one.
   • Connection Type: Select Proxy connection.
   • Agent: Select a DataArts Migration cluster as the connection agent. The DataArts Migration cluster and MRS cluster must be in the same region, AZ, VPC, and subnet, and the security group rule must allow communication between the two clusters. In this example, select the DataArts Migration cluster that is automatically created during DataArts Studio instance creation.

     To connect to an MRS 2.x cluster, select the DataArts Migration cluster of the 2.x version as the agent.

   Figure 3 Creating an MRS Hive data connection
   

Creating a Database

According to the implementation process of data lake governance, you are advised to create a database for each of the layers (SDI layer, DWI layer, DWR layer, and DM layer) in the data lake to implement hierarchical sharding. Data sharding is a concept involved in DataArts Architecture.

• Source Data Integration (SDI) copies data from the source system.
• Data Warehouse Integration (DWI) integrates and cleanses data from multiple source systems, and builds ER models based on the third normal form (3NF).
• Data Warehouse Report (DWR) is based on the multi-dimensional model and its data granularity is the same as that of the DWI layer.
• Data Mart (DM) is where multiple types of data are summarized and displayed.

Generally, create a database in the data lake service.

In this example, you can use either of the following methods to create a database in MRS Hive:

• You can create a database on the DataArts Factory module of DataArts Studio. For details, see Creating a Database.
• You can also develop and execute a SQL script for creating a database using the DataArts Studio DataArts Factory module or on the MRS client, and then use the script to create a database. For details about how to develop a script in DataArts Factory, see Developing an SQL Script. For details about how to develop a script using the MRS Client, see Using Hive from Scratch. Run the following Hive SQL commands to create a database:

  -- Create an SDI layer database.
  CREATE DATABASE demo_sdi_db;
  
  -- Create a DWI layer database.
  CREATE DATABASE demo_dwi_db;
  
  -- Create a DWR layer database.
  CREATE DATABASE demo_dwr_db;
  
  -- Create a DM layer database.
  CREATE DATABASE demo_dm_db;

Creating Tables

Based on sample data, create a source table to store raw data. To migrate data from a file to a database, you must create a destination table in advance. In this example, the data source is a CSV file on OBS instead of a database. When you use DataArts Studio DataArts Migration to migrate data to the cloud, the destination table cannot be automatically created. Therefore, you must create a table on the destination (MRS).

During data migration using DataArts Studio, a destination table can be automatically created for migration from relational databases to Hive and between relational databases. In this case, you do not need to create a table in the destination database in advance.

Run the following SQL statements to create a source table in the demo_sdi_db database to store raw data.

In this example, you can use either of the following methods to create a table in MRS Hive:

• You can create a table on the DataArts Studio DataArts Factory module. For details, see Creating a Table.
• You can also develop and execute a SQL script for creating a table using the DataArts Studio DataArts Factory module or on the MRS client, and then use the script to create a table. For details about how to develop a script in DataArts Factory, see Developing an SQL Script. For details about how to develop a script using the MRS Client, see Using Hive from Scratch. The following is an example Hive SQL command used to create a raw table in the demo_sdi_db database.

  DROP TABLE IF EXISTS `sdi_taxi_trip_data`;
  
  CREATE TABLE demo_sdi_db.`sdi_taxi_trip_data` (
    `VendorID` BIGINT COMMENT '',
    `tpep_pickup_datetime` TIMESTAMP COMMENT '',
    `tpep_dropoff_datetime` TIMESTAMP COMMENT '',
    `passenger_count` BIGINT COMMENT '',
    `trip_distance` DECIMAL(10,2) COMMENT '',
    `ratecodeid` BIGINT COMMENT '',
    `store_fwd_flag` STRING COMMENT '',
    `PULocationID` STRING COMMENT '',
    `DOLocationID` STRING COMMENT '',
    `payment_type` BIGINT COMMENT '',
    `fare_amount` DECIMAL(10,2) COMMENT '',
    `extra` DECIMAL(10,2) COMMENT '',
    `mta_tax` DECIMAL(10,2) COMMENT '',
    `tip_amount` DECIMAL(10,2) COMMENT '',
    `tolls_amount` DECIMAL(10,2) COMMENT '',
    `improvement_surcharge` DECIMAL(10,2) COMMENT '',
    `total_amount` DECIMAL(10,2) COMMENT ''
  );