E-MapReduce:Read from and write to StarRocks

Write data to StarRocks using Serverless Spark.

1. Create an SQL session. For more information, see Manage SQL sessions.

   When you create the session, select an engine version that matches your Spark Connector version. For Network Connection, select the network connection that you created in the previous step. In the Spark Configuration section, add the following parameter to load the Spark Connector.

   spark.emr.serverless.user.defined.jars  oss://<bucketname>/path/connector.jar

   Replace oss://<bucketname>/path/connector.jar with the OSS path of the Spark Connector that you uploaded in Step 1. For example, oss://emr-oss/spark/starrocks-spark-connector-3.5_2.12-1.1.2.jar.

2. On the Development page, create a SparkSQL task. In the upper-right corner, select the SQL session that you created.

   For more information, see Develop a SparkSQL job.

3. Copy the following code to the new SparkSQL tab. Modify the parameters as needed, and then click Run.

   CREATE TEMPORARY VIEW score_board
   USING starrocks
   OPTIONS
   (
     "starrocks.table.identifier" = "testdb.score_board",
     "starrocks.fe.http.url" = "<fe_host>:<fe_http_port>",
     "starrocks.fe.jdbc.url" = "jdbc:mysql://<fe_host>:<fe_query_port>",
     "starrocks.user" = "<user>",
     "starrocks.password" = "<password>"
   );
   
   INSERT INTO `score_board` VALUES (1, "starrocks", 100), (2, "spark", 100);

   The following table describes the parameters.

   • <fe_host>: The internal or public endpoint of the FE node in the Serverless StarRocks instance. You can find this on the FE Details tab of the Instance Details page.

     • If you use an internal endpoint, ensure that the services are in the same VPC.

     • If you use a public endpoint, ensure that the security group rules allow communication on the required ports. For more information, see Network access and security configuration.

   • <fe_http_port>: The HTTP port of the FE node in the Serverless StarRocks instance. The default port is 8030. You can find this on the FE Details tab of the Instance Details page.

   • <fe_query_port>: The query port of the FE node in the Serverless StarRocks instance. The default port is 9030. You can find this on the FE Details tab of the Instance Details page.

   • <user>: The username for the Serverless StarRocks instance. The default administrator username is admin. You can also add a new user on the User Management page. For more information about adding a user, see Manage users and grant permissions.

   • <password>: The password that corresponds to the <user>.

Query the written data using Serverless Spark.

In this example, a temporary view named test_view is created in the SparkSQL task to query data from score_board. Copy the following code to the new SparkSQL tab, select the code, and then click Run the selected(1000).

CREATE TEMPORARY VIEW test_view
USING starrocks
OPTIONS
(
   "starrocks.table.identifier" = "testdb.score_board",
   "starrocks.fe.http.url" = "<fe_host>:<fe_http_port>",
   "starrocks.fe.jdbc.url" = "jdbc:mysql://<fe_host>:<fe_query_port>",
   "starrocks.user" = "<user>",
   "starrocks.password" = "<password>"
);

SELECT * FROM test_view;

The output is shown in the following figure.

Write data to StarRocks using Serverless Spark.

1. Create a Notebook session. For more information, see Manage Notebook sessions.

   When you create the session, select an engine version that matches your Spark Connector version. For Network Connection, select the network connection that you created in the previous step. In the Spark Configuration section, add the following parameter to load the Spark Connector.

   spark.emr.serverless.user.defined.jars  oss://<bucketname>/path/connector.jar

   Replace oss://<bucketname>/path/connector.jar with the OSS path of the Spark Connector that you uploaded in Step 1. For example, oss://emr-oss/spark/starrocks-spark-connector-3.5_2.12-1.1.2.jar.

2. On the Data Development page, create an Interactive Development > Notebook task. In the upper-right corner, select the Notebook session that you created.

   For more information, see Manage Notebook sessions.

3. Copy the following code into a Python cell of the new Notebook and click Run.

   # Replace with your Serverless StarRocks configurations.
   fe_host = "<fe_host>"
   fe_http_port = "<fe_http_port>"
   fe_query_port = "<fe_query_port>"
   user = "<user>"
   password = "<password>"
   
   # Create a view.
   create_table_sql = f"""
   CREATE TEMPORARY VIEW score_board
   USING starrocks
   OPTIONS (
     "starrocks.table.identifier" = "testdb.score_board",
     "starrocks.fe.http.url" = "{fe_host}:{fe_http_port}",
     "starrocks.fe.jdbc.url" = "jdbc:mysql://{fe_host}:{fe_query_port}",
     "starrocks.user" = "{user}",
     "starrocks.password" = "{password}"
   )
   """
   
   spark.sql(create_table_sql)
   
   # Insert data.
   insert_data_sql = """
   INSERT INTO `score_board` VALUES (1, "starrocks", 100), (2, "spark", 100)
   """
   
   spark.sql(insert_data_sql)
   

   The following figure shows an example.

   

   The following table describes the parameters.

   • <fe_host>: The internal or public endpoint of the FE node in the Serverless StarRocks instance. You can find this on the FE Details tab of the Instance Details page.

     • If you use an internal endpoint, ensure that the services are in the same VPC.

     • If you use a public endpoint, ensure that the security group rules allow communication on the required ports. For more information, see Network access and security configuration.

   • <fe_http_port>: The HTTP port of the FE node in the Serverless StarRocks instance. The default port is 8030. You can find this on the FE Details tab of the Instance Details page.

   • <fe_query_port>: The query port of the FE node in the Serverless StarRocks instance. The default port is 9030. You can find this on the FE Details tab of the Instance Details page.

   • <user>: The username for the Serverless StarRocks instance. The default administrator username is admin. You can also add a new user on the User Management page. For more information about adding a user, see Manage users and grant permissions.

   • <password>: The password that corresponds to the <user>.

Query the written data using Serverless Spark.

In this example, add a new Python cell to create a temporary view named test_view. Use this view to query data from score_board. Copy the following code into the new Python cell and click the icon.

# Create a view.
create_view_sql=f"""
CREATE TEMPORARY VIEW test_view
USING starrocks
OPTIONS (
  "starrocks.table.identifier" = "testdb.score_board",
  "starrocks.fe.http.url" = "{fe_host}:{fe_http_port}",
  "starrocks.fe.jdbc.url" = "jdbc:mysql://{fe_host}:{fe_query_port}",
  "starrocks.user" = "{user}",
  "starrocks.password" = "{password}"
)
"""
spark.sql(create_view_sql)
  
# Query.
query_sql="SELECT * FROM test_view"
result_df = spark.sql(query_sql)
result_df.show()

The output is shown in the following figure.