使用英语阅读

通过


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 子句的逻辑。

字段包括 ConstantLimitClause

详情请见:重写 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 的结果,从而允许你修改表。

SQLGetTypeInfoSQLColumns 报告的数据类型之间不匹配时,通常使用此字段。

详情请见:重写 SQLGetTypeInfo
SQLTables 一个函数,可用于修改调用 SQLTables 返回的表元数据。
TolerateConcatOverflow 允许串联文本值,即使结果可能被截断以适应可用类型的范围。

例如,当在系统上将 VARCHAR(4000) 字段与 VARCHAR(4000) 字段连接起来时,即使结果可能被截断,连接也会折叠。

默认值:false
UseEmbeddedDriver (内部使用):逻辑值,用于控制是否应从本地目录加载 ODBC 驱动程序(使用 ODBC 4.0 规范中定义的新功能)。 通常只有 Microsoft 创建的随 Power Query 一起提供的连接器才会设置该值。

设置为 false 时,系统 ODBC 驱动程序管理器用于查找和加载驱动程序。

大多数连接器不需要设置此字段。

默认值:false

重写 AstVisitor

字段 AstVisitor 通过 Odbc.DataSource 选项记录设置。 用于修改针对特定查询方案生成的 SQL 语句。

备注

支持 LIMIT 和 OFFSET 子句(而不是 TOP)的驱动程序需要为 AstVisitor 提供 LimitClause 重写。

常数

为此值提供重写已弃用,并可能从之后的实现中删除。

LimitClause

此字段是一个函数,接收两个 Int64.Type 参数(skiptake),并返回一条包含两个文本字段(TextLocation)的记录。

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"
    ]

重写 SqlCapabilities

字段 详细信息
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 属于函数处理程序,用于接收对 SQLColumns 的 ODBC 调用结果。 源参数包含具有数据类型信息的表。 此重写通常用于修复 SQLGetTypeInfoSQLColumns 调用之间的数据类型不匹配问题。

有关源表参数格式的详细信息,请转到 SQLColumns 函数

重写 SQLGetFunctions

此字段用于重写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 */
]

重写 SQLGetInfo

此字段用于重写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 符合性级别,并使用属性 SQLGetInfoSQLGetFunctions 重写特定的 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

SQLGetTypeInfo 可以通过以下两种方式指定:

  • 包含与 ODBC 调用 SQLGetTypeInfo 相同类型信息的固定 table 值。
  • 接受表格参数并返回表格的函数。 该参数包含对 ODBC 调用 SQLGetTypeInfo 的原始结果。 函数实现可以修改或添加到此表。

第一种方法用于完全重写 ODBC 驱动程序返回的值。 如果要添加或修改这些值,则使用第二种方法。

有关类型表参数和预期返回值格式的详细信息,请转到 SQLGetTypeInfo 函数引用

使用静态表的 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                } }
);

使用函数的 SQLGetTypeInfo

以下代码片段将 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.DataSourceOdbc.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;

后续步骤