Odbc.DataSource 的参数
Odbc.DataSource 函数采用两个参数:connectionString
用于驱动程序,options
记录用于重写各种驱动程序行为。 通过选项记录,可以重写驱动程序报告的功能和其他信息,控制导航器行为,并影响 M 引擎生成的 SQL 查询。
受支持的选项记录字段分为两个类别:公共字段和始终可用字段,以及仅在扩展性上下文中可用的字段。
下表描述了选项记录中的公共字段。
字段 | 说明 |
---|---|
CommandTimeout |
持续时间值,用于控制服务器端查询在取消之前允许运行的时间。 默认:10 分钟 |
ConnectionTimeout |
持续时间值,用于控制在放弃尝试连接服务器之前需要等待的时间。 默认:60 秒 |
CreateNavigationProperties |
逻辑值,用于设置是否在返回的表上生成导航属性。 导航属性基于驱动程序报告的外键关系。 这些属性显示为可在查询编辑器中展开的“虚拟”列,从而创建相应的联接。 如果计算外键依赖项对驱动程序来说操作代价高昂,则可能需要将此值设置为 false。 默认值:true |
HierarchicalNavigation |
逻辑值,用于设置是否查看按架构名称分组的表。 设置为 false 时,表将显示在每个数据库下的平面列表中。 默认值:false |
SqlCompatibleWindowsAuth |
逻辑值,用于确定在使用 Windows 身份验证时是否生成与 SQL Server 兼容的连接字符串 - Trusted_Connection=Yes 。如果驱动程序支持 Windows 身份验证,但需要连接字符串中进行额外或重写设置,,则应将此值设置为 false,并使用下表中所述的 CredentialConnectionString 选项记录字段。默认值:true |
下表描述了只能通过扩展性获得的选项记录字段。 非简单字面值的字段将在后面的章节中介绍。
字段 | 说明 |
---|---|
AstVisitor |
包含一个或多个重写的记录,用于控制 SQL 查询生成。 此字段的最常见用法是为不支持 TOP 的驱动程序提供生成 LIMIT/OFFSET 子句的逻辑。 字段包括 Constant 和 LimitClause 。详情请见:重写 AstVisitor |
CancelQueryExplicitly |
逻辑值,指示 M 引擎在终止与 ODBC 服务器的连接之前,通过 ODBC 驱动程序明确取消任何正在运行的调用。 该字段适用于独立于服务器网络连接管理查询执行的情况,例如在某些 Spark 部署中。 在大多数情况下,不需要设置此值,因为当与服务器的网络连接终止时,服务器中的查询将取消。 默认值:false |
ClientConnectionPooling |
逻辑值,用于为 ODBC 驱动程序启用客户端连接池。 大多数驱动程序都希望将此值设置为 true。 默认值:false |
CredentialConnectionString |
文本或记录值,用于指定与凭据相关的连接字符串属性。 |
HideNativeQuery |
逻辑值,用于控制连接器是否在 Power Query 用户体验中显示生成的 SQL 语句。 仅当后端数据源本机支持 SQL-92 时,才应将其设置为 true。 默认值:false |
ImplicitTypeConversions |
包含驱动程序或后端服务器支持的隐式类型转换的表值。 此表中的值与驱动程序本身报告的转换相加。 在重写驱动程序报告的数据类型信息时,此字段通常与 SQLGetTypeInfo 字段一起使用。 |
OnError |
接收 record 类型的 errorRecord 参数时出现错误处理函数。此函数的常见用途包括处理 SSL 连接失败、提供下载链接(如果无法在系统上找到驱动程序)以及报告身份验证错误。 |
SoftNumbers |
当 SQL_CONVERT_* 功能不支持两种特定数字类型之间的转换时,允许 M 引擎选择兼容的数据类型。 默认值:false |
SqlCapabilities |
提供各种驱动程序功能重写的记录,也是指定 ODBC 3.8 未表达功能的一种方法。 详情请见:重写 SqlCapabilities |
SQLColumns |
可用于修改 SQLColumns 函数返回的列元数据的函数。详细请见:重写 SQLColumns |
SQLGetFunctions |
用于重写调用 SQLGetFunctions 所返回值的记录。此字段的常见用途是禁用参数绑定,或指定生成的查询应使用 CAST 而不是 CONVERT。 详情请见:重写 SQLGetFunctions |
SQLGetInfo |
用于重写调用 SQLGetInfo 所返回值的记录。详情请见:重写 SQLGetInfo |
SQLGetTypeInfo |
返回一个表的表或函数,该返回的表重写由 SQLGetTypeInfo 返回的类型信息。当该值设置为表时,该值将完全重写驱动程序报告的类型信息。 SQLGetTypeInfo 不会调用。当该值设置为函数时,函数将收到最初调用 SQLGetTypeInfo 的结果,从而允许你修改表。当 SQLGetTypeInfo 和 SQLColumns 报告的数据类型之间不匹配时,通常使用此字段。详情请见:重写 SQLGetTypeInfo |
SQLTables |
一个函数,可用于修改调用 SQLTables 返回的表元数据。 |
TolerateConcatOverflow |
允许串联文本值,即使结果可能被截断以适应可用类型的范围。 例如,当在系统上将 VARCHAR(4000) 字段与 VARCHAR(4000) 字段连接起来时,即使结果可能被截断,连接也会折叠。 默认值:false |
UseEmbeddedDriver |
(内部使用):逻辑值,用于控制是否应从本地目录加载 ODBC 驱动程序(使用 ODBC 4.0 规范中定义的新功能)。 通常只有 Microsoft 创建的随 Power Query 一起提供的连接器才会设置该值。 设置为 false 时,系统 ODBC 驱动程序管理器用于查找和加载驱动程序。 大多数连接器不需要设置此字段。 默认值:false |
字段 AstVisitor
通过 Odbc.DataSource 选项记录设置。 用于修改针对特定查询方案生成的 SQL 语句。
备注
支持 LIMIT 和 OFFSET 子句(而不是 TOP)的驱动程序需要为 AstVisitor
提供 LimitClause
重写。
为此值提供重写已弃用,并可能从之后的实现中删除。
此字段是一个函数,接收两个 Int64.Type
参数(skip
、take
),并返回一条包含两个文本字段(Text
、Location
)的记录。
LimitClause = (skip as nullable number, take as number) as record => ...
参数 skip
是要跳过的行数(即 OFFSET 的参数)。 如果未指定偏移量,则跳过值将为 null。 如果驱动程序支持 LIMIT,但不支持 OFFSET,则当跳过大于 0 时,该 LimitClause
函数应返回未实现的错误 (...)。
参数 take
表示要获取的行数(即 LIMIT 的参数)。
结果的 Text
字段包含要添加到已生成查询的 SQL 文本。
该 Location
字段指定插入子句的位置。 下表列出了支持的值。
值 | 说明 | 示例 |
---|---|---|
AfterQuerySpecification |
LIMIT 子句放在已生成 SQL 的末尾。 这是最常用的 LIMIT 语法。 |
SELECT a, b, c FROM 表 WHERE a > 10 LIMIT 5 |
BeforeQuerySpecification |
LIMIT 子句放在生成的 SQL 语句之前。 | LIMIT 5 ROWS SELECT a, b, c FROM 表 WHERE a > 10 |
AfterSelect |
LIMIT 在 SELECT 语句和任何修饰符(如 DISTINCT)之后执行。 | SELECT DISTINCT LIMIT 5 a, b, c FROM 表 WHERE a > 10 |
AfterSelectBeforeModifiers |
LIMIT 在 SELECT 语句之后,但在任何修饰符(如 DISTINCT)之前。 | SELECT LIMIT 5 DISTINCT a, b, c FROM 表 WHERE a > 10 |
以下代码片段为驱动程序提供 LimitClause 实现,该驱动程序期望使用带有可选 OFFSET 的 LIMIT 子句,格式如下:[OFFSET <offset> ROWS] LIMIT <row_count>
LimitClause = (skip, take) =>
let
offset = if (skip > 0) then Text.Format("OFFSET #{0} ROWS", {skip}) else "",
limit = if (take <> null) then Text.Format("LIMIT #{0}", {take}) else ""
in
[
Text = Text.Format("#{0} #{1}", {offset, limit}),
Location = "AfterQuerySpecification"
]
以下代码片段为支持 LIMIT 但不支持 OFFSET 的驱动程序提供 LimitClause
实现。 格式:LIMIT <row_count>
。
LimitClause = (skip, take) =>
if (skip > 0) then error "Skip/Offset not supported"
else
[
Text = Text.Format("LIMIT #{0}", {take}),
Location = "AfterQuerySpecification"
]
字段 | 详细信息 |
---|---|
FractionalSecondsScale |
介于 1 到 7 之间的数字值,表示毫秒值支持的小数位数。 此值应由想要启用查询折叠日期时间值的连接器设置。 默认值:null |
PrepareStatements |
逻辑值,表示应使用 SQLPrepare 编写语句。 默认值:false |
SupportsTop |
逻辑值,表示驱动程序支持 TOP 子句来限制返回的行数。 默认值:false |
StringLiteralEscapeCharacters |
文本值列表,指定转义字符串文本和 LIKE 表达式时要使用的字符。 示例: {""} 默认值:null |
SupportsDerivedTable |
表示驱动程序支持派生表(子选择)的逻辑值。 对于将一致性级别设置为 SQL_SC_SQL92_FULL(由驱动程序报告或使用 Sql92Conformance 设置重写)的驱动程序,该值假定为 true。 对于所有其他一致性级别,此值默认为 false。 如果驱动程序未报告 SQL_SC_SQL92_FULL 一致性级别,但支持派生表,请将此值设置为 true。 许多 DirectQuery 方案都需要支持派生表。 |
SupportsNumericLiterals |
表示生成的 SQL 是否应包含数值文本值的逻辑值。 设置为 false 时,始终使用参数绑定指定数值。 默认值:false |
SupportsStringLiterals |
表示生成的 SQL 是否应包含字符串字面量的逻辑值。 设置为 false 时,始终使用参数绑定指定字符串值。 默认值:false |
SupportsOdbcDateLiterals |
表示生成的 SQL 是否应包含日期字面量的逻辑值。 设置为 false 时,始终使用参数绑定指定日期值。 默认值:false |
SupportsOdbcTimeLiterals |
表示生成的 SQL 是否应包含时间字面量的逻辑值。 设置为 false 时,始终使用参数绑定指定时间值。 默认值:false |
SupportsOdbcTimestampLiterals |
表示生成的 SQL 是否应包含时间戳字面量的逻辑值。 设置为 false 时,始终使用参数绑定指定时间戳值。 默认值:false |
SQLColumns
属于函数处理程序,用于接收对 SQLColumns 的 ODBC 调用结果。 源参数包含具有数据类型信息的表。 此重写通常用于修复 SQLGetTypeInfo
和 SQLColumns
调用之间的数据类型不匹配问题。
有关源表参数格式的详细信息,请转到 SQLColumns 函数。
此字段用于重写ODBC 驱动程序返回的 SQLFunctions
值。 它包含一条记录,其字段名称等于为 ODBC SQLGetFunctions 函数定义的 FunctionId
常量。 可以在 ODBC 规范中找到每个字段的数值常量。
字段 | 详细信息 |
---|---|
SQL_CONVERT_FUNCTIONS |
表示执行类型转换时支持哪个函数。 默认情况下,M 引擎会尝试使用 CONVERT 函数。 倾向于使用 CAST 的驱动程序可以重写此值,报告仅支持 SQL_FN_CVT_CAST(数值为 0x2)。 |
SQL_API_SQLBINDCOL |
一个 (true/false) 逻辑值,表示糅合引擎在检索数据时是否应使用 SQLBindCol API。 如果设置为 false,则改用 SQLGetData。 默认值:false |
以下代码片段提供了一个明确指示 M 引擎使用 CAST 而不是 CONVERT 的示例。
SQLGetFunctions = [
SQL_CONVERT_FUNCTIONS = 0x2 /* SQL_FN_CVT_CAST */
]
此字段用于重写ODBC 驱动程序返回的 SQLGetInfo
值。 它包含一条记录,其字段名称等于为 ODBC SQLGetInfo 函数定义的 InfoType
常量。 可以在 ODBC 规范中找到每个字段的数值常量。 可以在糅合引擎跟踪文件中找到经检查的 InfoTypes
完整列表。
下表列出了常用的重写 SQLGetInfo
属性:
字段 | 详细信息 |
---|---|
SQL_SQL_CONFORMANCE |
整数值,表示驱动程序支持的 SQL-92 级别: (1) SQL_SC_SQL92_ENTRY:符合入门级别的 SQL-92。 (2) SQL_SC_FIPS127_2_TRANSITIONAL:符合 FIPS 127-2 过渡级别。 (4) SQL_SC_ SQL92_INTERMEDIATE" 符合中等级别 SQL-92。 (8) SQL_SC_SQL92_FULL:符合完全级别 SQL-92。 在 Power Query 方案中,连接器在只读模式下使用。 大多数驱动程序希望报告 SQL_SC_SQL92_FULL 符合性级别,并使用属性 SQLGetInfo 和 SQLGetFunctions 重写特定的 SQL 生成行为。 |
SQL_SQL92_PREDICATES |
SQL-92 中定义的枚举 SELECT 语句中支持的谓词的位掩码。 转到 ODBC 规范中的 SQL_SP_* 常量。 |
SQL_AGGREGATE_FUNCTIONS |
枚举聚合函数支持的位掩码。 SQL_AF_ALL SQL_AF_AVG SQL_AF_COUNT SQL_AF_DISTINCT SQL_AF_MAX SQL_AF_MIN SQL_AF_SUM 转到 ODBC 规范中的 QL_AF_* 常量。 |
SQL_GROUP_BY |
整数值,用于指定 GROUP BY 子句中的列与选择列表中的非聚合列之间的关系: SQL_GB_COLLATE:可以在每个分组列的末尾指定 COLLATE 子句。 SQL_GB_NOT_SUPPORTED:不支持 GROUP BY 子句。 SQL_GB_GROUP_BY_EQUALS_SELECT:GROUP BY 子句必须包含选择列表中的所有非聚合列。 不能包含任何其他列。 例如,SELECT DEPT、MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT。 SQL_GB_GROUP_BY_CONTAINS_SELECT:GROUP BY 子句必须包含选择列表中的所有非聚合列。 可以包含不在选择列表中的列。 例如,SELECT DEPT、MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT、AGE。 SQL_GB_NO_RELATION:GROUP BY 子句中的列和选择列表不相关。 选择列表中的非分组非聚合列的含义取决于数据源。 例如,SELECT DEPT、SALARY FROM EMPLOYEE GROUP BY DEPT、AGE。 转到 ODBC 规范中的 SQL_GB_* 常量。 |
以下帮助程序函数可用于从整数值列表中创建位掩码值:
Flags = (flags as list) =>
let
Loop = List.Generate(
()=> [i = 0, Combined = 0],
each [i] < List.Count(flags),
each [i = [i]+1, Combined =*Number.BitwiseOr([Combined], flags{i})],
each [Combined]),
Result = List.Last(Loop, 0)
in
Result;
SQLGetTypeInfo
可以通过以下两种方式指定:
- 包含与 ODBC 调用
SQLGetTypeInfo
相同类型信息的固定table
值。 - 接受表格参数并返回表格的函数。 该参数包含对 ODBC 调用
SQLGetTypeInfo
的原始结果。 函数实现可以修改或添加到此表。
第一种方法用于完全重写 ODBC 驱动程序返回的值。 如果要添加或修改这些值,则使用第二种方法。
有关类型表参数和预期返回值格式的详细信息,请转到 SQLGetTypeInfo 函数引用。
以下代码片段提供了 SQLGetTypeInfo
的静态实现。
SQLGetTypeInfo = #table(
{ "TYPE_NAME", "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREF", "LITERAL_SUFFIX", "CREATE_PARAS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERNAL_PRECISION", "USER_DATA_TYPE" }, {
{ "char", 1, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "char", null, null, -8, null, null, 0, 0 },
{ "int8", -5, 19, "'", "'", null, 1, 0, 2, 0, 10, 0, "int8", 0, 0, -5, null, 2, 0, 0 },
{ "bit", -7, 1, "'", "'", null, 1, 1, 3, null, 0, null, "bit", null, null, -7, null, null, 0, 0 },
{ "bool", -7, 1, "'", "'", null, 1, 1, 3, null, 0, null, "bit", null, null, -7, null, null, 0, 0 },
{ "date", 9, 10, "'", "'", null, 1, 0, 2, null, 0, null, "date", null, null, 9, 1, null, 0, 0 },
{ "numeric", 3, 28, null, null, null, 1, 0, 2, 0, 0, 0, "numeric", 0, 0, 2, null, 10, 0, 0 },
{ "float8", 8, 15, null, null, null, 1, 0, 2, 0, 0, 0, "float8", null, null, 6, null, 2, 0, 0 },
{ "float8", 6, 17, null, null, null, 1, 0, 2, 0, 0, 0, "float8", null, null, 6, null, 2, 0, 0 },
{ "uuid", -11, 37, null, null, null, 1, 0, 2, null, 0, null, "uuid", null, null, -11, null, null, 0, 0 },
{ "int4", 4, 10, null, null, null, 1, 0, 2, 0, 0, 0, "int4", 0, 0, 4, null, 2, 0, 0 },
{ "text", -1, 65535, "'", "'", null, 1, 1, 3, null, 0, null, "text", null, null, -10, null, null, 0, 0 },
{ "lo", -4, 255, "'", "'", null, 1, 0, 2, null, 0, null, "lo", null, null, -4, null, null, 0, 0 },
{ "numeric", 2, 28, null, null, "precision, scale", 1, 0, 2, 0, 10, 0, "numeric", 0, 6, 2, null, 10, 0, 0 },
{ "float4", 7, 9, null, null, null, 1, 0, 2, 0, 10, 0, "float4", null, null, 7, null, 2, 0, 0 },
{ "int2", 5, 19, null, null, null, 1, 0, 2, 0, 10, 0, "int2", 0, 0, 5, null, 2, 0, 0 },
{ "int2", -6, 5, null, null, null, 1, 0, 2, 0, 10, 0, "int2", 0, 0, 5, null, 2, 0, 0 },
{ "timestamp", 11, 26, "'", "'", null, 1, 0, 2, null, 0, null, "timestamp", 0, 38, 9, 3, null, 0, 0 },
{ "date", 91, 10, "'", "'", null, 1, 0, 2, null, 0, null, "date", null, null, 9, 1, null, 0, 0 },
{ "timestamp", 93, 26, "'", "'", null, 1, 0, 2, null, 0, null, "timestamp", 0, 38, 9, 3, null, 0, 0 },
{ "bytea", -3, 255, "'", "'", null, 1, 0, 2, null, 0, null, "bytea", null, null, -3, null, null, 0, 0 },
{ "varchar", 12, 65535, "'", "'", "max. length", 1, 0, 2, null, 0, null, "varchar", null, null, -9, null, null, 0, 0 },
{ "char", -8, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "char", null, null, -8, null, null, 0, 0 },
{ "text", -10, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "text", null, null, -10, null, null, 0, 0 },
{ "varchar", -9, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "varchar", null, null, -9, null, null, 0, 0 },
{ "bpchar", -8, 65535, "'", "'", "max. length", 1, 1, 3, null, 0, null, "bpchar", null, null, -9, null, null, 0, 0 } }
);
以下代码片段将 bpchar
类型追加到驱动程序返回的现有类型中。
SQLGetTypeInfo = (types as table) as table =>
let
newTypes = #table(
{
"TYPE_NAME",
"DATA_TYPE",
"COLUMN_SIZE",
"LITERAL_PREF",
"LITERAL_SUFFIX",
"CREATE_PARAS",
"NULLABLE",
"CASE_SENSITIVE",
"SEARCHABLE",
"UNSIGNED_ATTRIBUTE",
"FIXED_PREC_SCALE",
"AUTO_UNIQUE_VALUE",
"LOCAL_TYPE_NAME",
"MINIMUM_SCALE",
"MAXIMUM_SCALE",
"SQL_DATA_TYPE",
"SQL_DATETIME_SUB",
"NUM_PREC_RADIX",
"INTERNAL_PRECISION",
"USER_DATA_TYPE"
},
// we add a new entry for each type we want to add
{
{
"bpchar",
-8,
65535,
"'",
"'",
"max. length",
1,
1,
3,
null,
0,
null,
"bpchar",
null,
null,
-9,
null,
null,
0,
0
}
}),
append = Table.Combine({types, newTypes})
in
append;
ODBC 驱动程序的连接字符串通过 Odbc.DataSource 和 Odbc.Query 函数的第一个参数设置。 该值可以是文本,也可以是 M 记录。 使用记录时,记录中的每个字段都将成为连接字符串中的属性。 所有连接字符串都需要字段 Driver
(或如果需要用户预配置系统级 DSN,则需要 DSN
字段)。 凭据相关的属性需单独设置。 其他属性特定于驱动程序。
以下代码片段显示了新数据源函数的定义、ConnectionString
记录的创建以及 Odbc.DataSource 函数的调用。
[DataSource.Kind="SqlODBC", Publish="SqlODBC.Publish"]
shared SqlODBC.Contents = (server as text) =>
let
ConnectionString = [
Driver = "SQL Server Native Client 11.0",
Server = server,
MultiSubnetFailover = "Yes",
ApplicationIntent = "ReadOnly",
APP = "PowerBICustomConnector"
],
OdbcDatasource = Odbc.DataSource(ConnectionString)
in
OdbcDatasource;