作者:手机用户2502911805 | 来源:互联网 | 2023-09-04 14:12
我在MongoDB中有Employee表,其中有employee_id,名称列。
我在MySQL中有另一个表Salary,带有emp_id,金额列。
我必须使用方解石适配器在2个表之间进行联接。
请建议如何完成此操作。
编辑:我尝试过的代码和异常。
型号:
{
"version":"1.0","defaultSchema":"spark_sql","schemas":[
{
"type":"jdbc","name":"spark_sql","jdbcUser":"root","jdbcPassword":"root","jdbcUrl":"jdbc:mysql://localhost/spark_sql","jdbcSchema":"spark_sql"
},{
"name":"employee_salary","type":"custom","factory":"org.apache.calcite.adapter.mongodb.MongoSchemaFactory","operand":{
"host":"localhost:27017","database":"employee_salary"
}
},{
"name":"syncnicia","database":"syncnicia"
}
},{
"name":"subodh34","factory":"com.syncnicia.testbais.excel.ExcelSchemaFactory","operand":{
"directory":"sheets/"
}
},{
"type":"jdbc","name":"SQL_Schema","jdbcUser":"admin","jdbcPassword":"admin","jdbcUrl":"jdbc:sqlserver://192.168.0.13","jdbcSchema":"SQL_Schema"
}
]
}
Java:
Connection cOnnection= DriverManager.getconnection("jdbc:calcite:model=src/main/resources/model.json");
CalciteConnection calciteCOnnection= connection.unwrap(CalciteConnection.class);
Statement statement7 = calciteConnection.createStatement();
ResultSet resultset7 = statement7.executeQuery("SELECT * FROM \"spark_sql\".\"employee\" as e JOIN \"employee_salary\".\"salary\" as es ON e.\"emp_id\" = es.\"_MAP['employee_id']\"");
例外:
java.sql.SQLException: Error while executing SQL "SELECT 1 FROM "employee_salary"."salary" as "s" JOIN "employee_salary"."employee" as "e" ON "e"."_MAP['emp_id']" = "s"."_MAP['employee_id']"": From line 1,column 97 to line 1,column 112: Column '_MAP['emp_id']' not found in table 'e'
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
at com.syncnicia.testbais.main.EntryClass.byModel(EntryClass.java:95)
at com.syncnicia.testbais.main.EntryClass.main(EntryClass.java:27)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,column 112: Column '_MAP['emp_id']' not found in table 'e'