Shulou(Shulou.com)06/01 Report--

Editor to share with you how to import GaussDW into MRS-Hive data sources, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Prepare the environment

A DWS cluster has been created. Make sure that the MRS and DWS clusters are in the same region, availability zone, and same VPC subnet, and that the cluster networks are interconnected.

Basic process

The expected duration of this practice is: 1 hour, the basic process is as follows:

1. Create a MRS analysis cluster (select Hive, Spark, Tez components).

2. Upload the local txt data file to the OBS bucket, import the Hive through the OBS bucket, and import the ORC storage table from the txt storage table.

3. Create a MRS data source connection.

4. create an external server.

5. Create an appearance.

6. Import DWS local surface through appearance.

First, create a MRS analysis cluster

1. Log in to Huawei Cloud console, select "EI Enterprise Intelligence > MapReduce Service", click "purchase Cluster", select "Custom purchase", enter the software configuration parameters, and click "next".

2. Fill in the hardware configuration parameters and click "next".

3. Fill in the advanced configuration parameters as follows, click "Buy now", and wait about 15 minutes for the cluster to be created successfully.

Prepare the ORC data source of MRS

1. Create a new product_info.txt for local PC, copy the following data and save it locally.

100fugXHDKLFJ3Power2017-09-01Gravity Autumn New Shirt Women,red,M,328,2017-09-04Power715 Autumn New Shirt Women,red,M,328,2017-09-04Power715good205FL5MY 2017-09-01MIT Amem 2017 Autumn New Knitwear Women,pink,L,584,2017-09-01MIT 406 very goodfugil FJOLFI 1937copyright pV7MY 2017-09-01My AMY 2017 autumn new T-shirt men,red,XL,1245,2017-09-01MIT Bad.310QPXMAR 3956authoraD8method QPX Lrecinct 411 really super nice150,ABEF-C-1820-#mC6,2017-09-05 436 autumn new casual pants men,black,L,997,2017-09-10 seller's packaging is exquisite200,BCQP-E-2365-#qE4,2017-09-10 seller's packaging is exquisite200,BCQP-E-2365-#qE4,2017-09-10 seller's packaging is exquisite200,BCQP-E-2365-#qE4,2017-09-10 seller's packaging is exquisite200,BCQP-E-2365-#qE4,2017-09-10, 2017 autumn new dress women,black,S,841,2017-09-10, 301, the clothes are of good quality.250,EABE-D-1476-#oB1,2017-09-10, the clothes are of good quality.250,EABE-D-1476-#oB1,2017-09-10. Follow the store for a long time.108,CDXK-F-1527-#pL2,2017-09-11 autumn new jacket women,white,M,114,2017-09-14 Follow the store for a long time.108,CDXK-F-1527-#pL2,2017-09-14 really amazing to buy450,MMCE-H-4728-#nP9,2017 2017 autumn new woolen coat women,red,L,2004,2017-09-14 really amazing to buy450,MMCE-H-4728-#nP9,2017-09-14 autumn new woolen coat women,red,L,2004,2017 22 autumn new woolen coat women,red,L,2004,2017-09-14 really amazing to buy450,MMCE-H-4728-#nP9,2017-09-14 Magi 2017 Open the package and the clothes have no odor260,OCDA-G-2817-#bD3,2017-09-12 BJournal 2017 autumn new woolen coat women,red,L,2004,2017-09-15 Follow the store for a long time.108,CDXK-F-1527-#pL2,2017-09-15 autumn new woolen coat women,red,L,2004,2017-09-15 Personality favorite clothes980 ZKDS-J-5490-#cW4,2017-09-13 autumn new shoes men,green,M,4345,2017-09-16 Autumn New Women's Cotton Clothing,red,M,112,2017-09-16 autumn new shoes men,green,M,4345,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new shoes men,green,M,4345,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new shoes men,green,M,4345,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new shoes men,green,M,4345,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new shoes men,green,M,4345,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new shoes men,green,M,4345,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017-09-16 autumn new underwear men,yellow,37,2840,2017 GKLW-l-2897-#wQ7,2017-09-22 clothes are very comfortable to wear300,HWEC-L-2531-#xP8,2017 Autumn New Jeans Men,blue,39,5879,2017-09-25 7200 Autumn New Wide Leg Pants Women,black,M,3045,2017-09-25 the clothes are very comfortable to wear300,HWEC-L-2531-#xP8,2017-09-23 GKLW-l-2897-#wQ7,2017 2017 autumn new shoes women,brown,M,403,2017-09-26 people 607 autumn new shoes women,brown,M,403,2017-09-26 people 607 autumn new shoes women,brown,M,403,2017-09-26 people 607 autumn new shoes women,brown,M,403,2017-09-26 507 autumn new shoes women,brown,M,403,2017-09-26 people 607 autumn new shoes women,brown,M,403,2017-09-26 507-09-26 507 good 100 people IQPD Lishi 3214 colors Q1 Autumn New Underwear Women,red 2017-09-24 Autumn New Wide Leg Pants Women,black,M,3045,2017 2017 Autumn New Wide Leg Pants Women,black,M,3045,2017-09-27 very good.350,LPEC-N-4572-#zX2,2017-09-25 BMague 2017 Autumn New Underwear Women,red The seller's service is very good110,NQAB-O-3768-#sM3,2017-09-26, the seller's service is very good110,NQAB-O-3768-#sM3,2017-09-26, the color is very good, 7021, the, the, the Autumn New Clothes with Chiffon Shirt,black,M,2056,2017, the, the, the Very good

Log in to the OBS console, click "create bucket", fill in the following parameters, and click "create now".

3. Wait for the bucket to be created, click the bucket name, select "object > upload object", and upload the product_info.txt to the OBS bucket.

4. Switch back to the MRS console, click the created MRS cluster name, go to "Overview", click "Click Sync" on the row of "IAM user synchronization", and wait for the synchronization to be completed in about 5 minutes.

5. Go back to the MRS cluster page, click "Node Management", click any master node, go to the node page, switch to "Elastic Public Network IP", click "bind Elastic Public Network IP", check the existing elastic IP and click "OK". If not, create it. Record this public network IP.

6. Confirm the primary master node.

Use the SSH tool to log in to the above node as the root user with the root password of Huawei_12345 and switch to the omm user.

Su-omm

Execute the following command to query the primary master node, and echo the node with the "HAActive" parameter value of "active" as the primary master node.

Sh ${BIGDATA_HOME} / om-0.0.1/sbin/status-oms.sh

7. Log in to the main master node using the root user, switch to the omm user, and enter the directory where the Hive client is located.

Su-omm

Cd / opt/client

8. Create a table product _ info with storage type TEXTFILE on Hive.

Under the / opt/client path, import the environment variables.

Source bigdata_env

Log in to the Hive client.

Beeline

Execute the following SQL statement in turn to create the demo database and table product_info.

CREATE DATABASE demo;USE demo;DROP TABLE product_info CREATE TABLE product_info (product_price int not null, product_id char (30) not null, product_time date, product_level char (10), product_name varchar (200) Product_type1 varchar (20), product_type2 char (10), product_monthly_sales_cnt int, product_comment_time date, product_comment_num int, product_comment_content varchar) row format delimited fields terminated by', 'stored as TEXTFILE

9. Import the product_info.txt data file into Hive.

Switch back to the MRS cluster, click File Management, and click Import data.

OBS path: select the OBS bucket name created above, locate the product_info.txt file, and click Yes.

HDFS path: select / user/hive/warehouse/demo.db/product_info/, and click Yes.

Click OK and wait for the import to succeed, when the table data for product_info has been imported successfully.

Create the ORC table and import the data into the ORC table.

Execute the following SQL statement to create the ORC table.

DROP TABLE product_info_orc CREATE TABLE product_info_orc (product_price int not null, product_id char (30) not null, product_time date, product_level char (10), product_name varchar (200) Product_type1 varchar (20), product_type2 char (10), product_monthly_sales_cnt int, product_comment_time date, product_comment_num int, product_comment_content varchar) row format delimited fields terminated by', 'stored as orc

Insert data from the product_info table into the Hive ORC table product_info_orc.

Insert into product_info_orc select * from product_info

Query ORC table data was imported successfully.

Select * from product_info_orc; III. Create a MRS data source connection

Log in to the DWS management console, click the created DWS cluster, and make sure that the DWS cluster is in the same region, available partition, and under the same VPC subnet as MRS.

Switch to MRS data Source and click create MRS data Source connection.

Select the previous step to create a data source named "MRS01", user name: admin, password: Huawei@12345, click OK, and the creation is successful.

Create an external server

(1) use Data Studio to connect to the created DWS cluster.

(2) create a user dbuser with the permission to create the database:

CREATE USER dbuser WITH CREATEDB PASSWORD "Bigdata@123"

(3) switch to the newly created dbuser user:

SET ROLE dbuser PASSWORD "Bigdata@123"

(4) create a new mydatabase database:

CREATE DATABASE mydatabase

(5) perform the following steps to switch to connect to the newly created mydatabase database.

In the object browser window of the Data Studio client, right-click the database connection name, click Refresh in the pop-up menu, and you can see the new database after refreshing.

Right-click the mydatabase database name, and click Open connection in the pop-up menu.

Right-click the "mydatabase" database name and click "Open New Terminal" in the pop-up menu to open the SQL command window that connects to the specified database. Please perform all the following steps in this command window.

(6) Grant the dbuser user the right to create an external server:

GRANT ALL ON FOREIGN DATA WRAPPER hdfs_fdw TO dbuser

Where the name of FOREIGN DATA WRAPPER can only be hdfs_fdw,dbuser, which is the user name that created the SERVER.

(7) execute the following command to give the user permission to use appearance.

ALTER USER dbuser USEFT

(8) switch back to the Postgres system database and query the external server automatically created by the system after the MRS data source is created.

SELECT * FROM pg_foreign_server

The returned results are as follows:

Srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions---+ -+- -- gsmpp_server | 10 | 13673 | gsmpp_errorinfo_server | 10 | 13678 | hdfs_server_8f79ada0_ D998_4026_9020_80d6de2692ca | 16476 | 13685 | {"address=192.168.1.245:9820192.168.1.218:9820" Hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692cajol typewritten HDFS} (3 rows)

(9) switch to mydatabase database and switch to dbuser user.

SET ROLE dbuser PASSWORD "Bigdata@123"

(10) create an external server.

The SERVER name, address, and configuration path can be kept consistent with 8.

CREATE SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address' 192.168.1.245) 9820192.168.1.218 MRS 9820, / / the intranet IP of the Master master / slave node of the DWS management side, can communicate with DWS. Hdfscfgpath'/ MRS/8f79ada0-d998-4026-9020-80d6de2692causal hdfs')

(11) View the external server.

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca'

The returned result is as follows, indicating that it has been created successfully:

Srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions---+ -+- -- hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca | 16476 | 13685 | {"address=192.168.1.245:9820192.168.1.218:29820" Hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692cajol typewritten HDFS} (1 row) 5. Create appearance

1. Get the file path of the product_info_orc of Hive.

Log in to the MRS Management console.

Select Cluster list > existing Cluster, click the name of the cluster you want to view, and go to the basic information page of the cluster.

Click File Management and select HDFS File list.

Enter the storage directory where you want to import the data into the GaussDB (DWS) cluster and record its path.

Figure 1 View the data storage path on MRS

2. Create an appearance. Fill in the name of the external server created by 10 in the name of SERVER and the path found in 1 in foldername.

DROP FOREIGN TABLE IF EXISTS foreign_product_info CREATE FOREIGN TABLE foreign_product_info (product_price integer not null, product_id char (30) not null, product_time date, product_level char (10), product_name varchar (200), product_type1 varchar (20) Product_type2 char (10), product_monthly_sales_cnt integer, product_comment_time date, product_comment_num integer, product_comment_content varchar) SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca OPTIONS (format 'orc', encoding' utf8' Foldername'/ user/hive/warehouse/demo.db/product_info_orc/') DISTRIBUTE BY ROUNDROBIN VI. Perform data import

1. Create a local target table.

DROP TABLE IF EXISTS product_info CREATE TABLE product_info (product_price integer not null, product_id char (30) not null, product_time date, product_level char (10), product_name varchar (200), product_type1 varchar (20) Product_type2 char (10), product_monthly_sales_cnt integer, product_comment_time date, product_comment_num integer, product_comment_content varchar (200) with (orientation = column,compression=middle) DISTRIBUTE BY HASH (product_id)

2. Import the target table from the appearance.

INSERT INTO product_info SELECT * FROM foreign_product_info

3. Query the import results.

SELECT * FROM product_info; above are all the contents of the article "how GaussDW imports MRS-Hive data sources". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.