我想构建一个API,用于查询databricks的表格,并将结果输出为JSON。实现此目标的一种方法是调用databricks的Jobs REST API来执行作业并读取作业输出,但这种方法有数据大小限制(最大为5MB,而我的API结果集可能超过20MB)。相反,我能否使用由集群提供的JDBC/ODBC端点使用Microsoft.Net连接到databricks,或者是否有其他直接连接的方式?我的API层最好在Microsoft.Net中构建。然而,我也愿意尝试Python。
我找到了几种连接到databricks集群的方法:
我选择了选项#1,并提供以下C#示例代码。
// Build connection string
OdbcConnectionStringBuilder odbcConnectionStringBuilder = new OdbcConnectionStringBuilder
{
Driver = "Simba Spark ODBC Driver"
};
odbcConnectionStringBuilder.Add("Host", "adb-xxxxxxxxxxxxx.7.xxxxxxxbricks.net");
odbcConnectionStringBuilder.Add("Port", "443");
odbcConnectionStringBuilder.Add("SSL", "1");
odbcConnectionStringBuilder.Add("ThriftTransport", "2");
odbcConnectionStringBuilder.Add("AuthMech", "3");
odbcConnectionStringBuilder.Add("UID", "token");
odbcConnectionStringBuilder.Add("PWD", "<Access token generated in databricks>");
odbcConnectionStringBuilder.Add("HTTPPath", "sql/protocolv1/o/xxxxxxxxxxxxxxx/yyyy8-dfcccf-tyyujjk8");
using (OdbcConnection connection = new OdbcConnection(odbcConnectionStringBuilder.ConnectionString))
{
string sqlQuery = "select * from yourdb.TableName";
OdbcCommand command = new OdbcCommand(sqlQuery, connection);
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetName(i) + "\t");
}
Console.Write("\n");
reader.Close();
command.Dispose();
}