MSSQL

您可以直接从 Supabase 控制面板启用MSSQL包装器。

Microsoft SQL Server 是由 Microsoft 开发的专有关系数据库管理系统。

SQL Server Wrapper 允许您在 Postgres 数据库中读取 Microsoft SQL Server 中的数据。

准备#

在您能够查询 SQL Server 之前，您需要启用 Wrappers 扩展并在 Postgres 中存储您的凭据。

启用 Wrappers#

确保 wrappers 扩展已安装在您的数据库上

1
create extension if not exists wrappers with schema extensions;

启用 SQL Server Wrapper#

启用 mssql_wrapper FDW

1
create foreign data wrapper mssql_wrapper
2
  handler mssql_fdw_handler
3
  validator mssql_fdw_validator;

存储您的凭据（可选)#

默认情况下，Postgres 将 FDW 凭据以明文形式存储在 pg_catalog.pg_foreign_server 中。任何访问此表的人都可以查看这些凭据。Wrappers 旨在与 Vault 配合使用，Vault 为存储凭据提供额外的安全级别。我们建议使用 Vault 存储您的凭据。

1
-- Save your SQL Server connection string in Vault and retrieve the created `key_id`
2
select vault.create_secret(
3
  'Server=localhost,1433;User=sa;Password=my_password;Database=master;IntegratedSecurity=false;TrustServerCertificate=true;encrypt=DANGER_PLAINTEXT;ApplicationName=wrappers',
4
  'mssql',
5
  'MS SQL Server connection string for Wrappers'
6
);

连接字符串是一个 ADO.NET 连接字符串，它以分号分隔的字符串指定连接参数。

支持的参数

所有参数键均不区分大小写。

参数	允许的值	描述
服务器	`<string>`	要连接到的 SQL Server 实例的名称或网络地址。格式：`host,port`
User	`<string>`	SQL Server 登录帐户。
密码	`<string>`	登录 SQL Server 帐户的密码。
数据库	`<string>`	数据库的名称。
IntegratedSecurity	false	Windows/Kerberos 身份验证和 SQL 身份验证。
TrustServerCertificate	true, false	指定在使用 TLS 连接时是否信任服务器证书。
Encrypt	true, false, DANGER_PLAINTEXT	指定驱动程序是否使用 TLS 加密通信。
ApplicationName	`<string>`	设置连接的应用程序名称。

连接到 SQL Server#

我们需要向 Postgres 提供连接到 SQL Server 的凭据。我们可以使用 create server 命令来执行此操作

1
create server mssql_server
2
  foreign data wrapper mssql_wrapper
3
  options (
4
    conn_string_id '<key_ID>' -- The Key ID from above.
5
  );

创建模式#

我们建议创建一个模式来保存所有外部表

1
create schema if not exists mssql;

选项#

完整的外部表选项如下

table - SQL Server 中的源表或视图名称，必需。

这也可以是一个用括号括起来的子查询，例如，

1
table '(select * from users where id = 42 or id = 43)'

实体#

SQL Server 表#

这是一个代表 SQL Server 表和视图的对象。

参考：Microsoft SQL Server 文档

操作#

对象	选择	插入	更新	删除	截断
table/view	✅	❌	❌	❌	❌

用法#

1
create foreign table mssql.users (
2
  id bigint,
3
  name text,
4
  dt timestamp
5
)
6
  server mssql_server
7
  options (
8
    table 'users'
9
  );

说明#

支持将表和视图作为数据源
可以在 table 选项中使用子查询
支持以下内容的查询下推
- where 子句
- order by 子句
- limit 子句
有关 PostgreSQL 和 SQL Server 之间类型映射的详细信息，请参阅“数据类型”部分

查询下推支持#

此 FDW 支持 where、order by 和 limit 子句下推。

支持的数据类型#

Postgres 类型	SQL Server 类型
boolean	bit
char	tinyint
smallint	smallint
real	float(24)
integer	int
double precision	float(53)
bigint	bigint
numeric	numeric/decimal
text	varchar/char/text
date	date
timestamp	datetime/datetime2/smalldatetime
timestamptz	datetime/datetime2/smalldatetime

限制#

本节描述了在使用此 FDW 时需要注意的重要限制和注意事项

由于需要完全的数据传输，大型结果集可能会遇到较慢的性能
仅支持 PostgreSQL 和 SQL Server 之间特定的数据类型映射
仅支持读取操作（无 INSERT、UPDATE、DELETE 或 TRUNCATE）
不支持 Windows 身份验证（集成安全性）
使用这些外部表的物化视图在逻辑备份期间可能会失败

示例#

基本示例#

首先，在 SQL Server 中创建一个源表

1
-- Run below SQLs on SQL Server to create source table
2
create table users (
3
  id bigint,
4
  name varchar(30),
5
  dt datetime2
6
);
7
8
-- Add some test data
9
insert into users(id, name, dt) values (42, 'Foo', '2023-12-28');
10
insert into users(id, name, dt) values (43, 'Bar', '2023-12-27');
11
insert into users(id, name, dt) values (44, 'Baz', '2023-12-26');

然后在 PostgreSQL 中创建并查询外部表

1
create foreign table mssql.users (
2
  id bigint,
3
  name text,
4
  dt timestamp
5
)
6
  server mssql_server
7
  options (
8
    table 'users'
9
  );
10
11
select * from mssql.users;

远程子查询示例#

使用子查询创建一个外部表

1
create foreign table mssql.users_subquery (
2
  id bigint,
3
  name text,
4
  dt timestamp
5
)
6
  server mssql_server
7
  options (
8
    table '(select * from users where id = 42 or id = 43)'
9
  );
10
11
select * from mssql.users_subquery;