E-MapReduce:Read from and write to StarRocks

Write data to StarRocks by using EMR Serverless Spark.

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

   When creating a session, select the engine version that corresponds to the Spark Connector version, select the network connection created in the previous step in Network Connection, and add the following parameters in Spark Configuration 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. Then, select the SQL session you created from the upper-right corner.

   For more information, see Develop a SparkSQL job.

3. Copy the following code to the new SparkSQL tab, replace the placeholder values 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);

   Parameter description:

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

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

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

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

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

   • <user>: The username for the Serverless StarRocks instance. By default, an admin user with administrator privileges is provided. You can also add new users to connect by using the User Management page. For more information about how to add users, see Manage Users and Data Authorization.

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

Query the written data by using EMR Serverless Spark.

In this example, we create a temporary view named test_view in the Spark SQL task and use it to query data from score_board. Copy the following code into a new Spark SQL tab, select the code, and then click Run the selected.

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;

Output

The query returns two records: (1, "starrocks", 100) and (2, "spark", 100).

Write data to StarRocks by using EMR Serverless Spark.

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

   When creating a session, select the engine version that corresponds to the Spark Connector version, select the network connection created in the previous step in Network Connection, and add the following parameters in Spark Configuration 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 an Interactive Development > Notebook task, and then select the created Notebook session in the upper-right corner.

   For more information, see Manage Notebook sessions.

3. Copy the following code to a Python cell in the Notebook and click Run.

   # Replace the placeholders with your EMR 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)
   

   Parameter description:

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

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

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

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

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

   • <user>: The username for the Serverless StarRocks instance. By default, an admin user with administrator privileges is provided. You can also add new users to connect by using the User Management page. For more information about how to add users, see Manage Users and Data Authorization.

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

Query the written data by using EMR Serverless Spark.

In a new Python cell, create a temporary view named test_view to query the score_board table. Copy the following code into the cell and click the run icon () to execute it.

# 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 the data.
query_sql="SELECT * FROM test_view"
result_df = spark.sql(query_sql)
result_df.show()

Output

+---+---------+-----+
| id|     name|score|
+---+---------+-----+
|  2|    spark|  100|
|  1|starrocks|  100|
+---+---------+-----+