install SQL Server to Oracle Linux 8 - end0tknr's kipple - web写経開発
先日の上記entryの続きです。
今回は、SQL Server 2022 for Linux に対し、
utf-8で記載されたcsvファイルをbcpコマンドでインポートします。
目次
utf-8のデータベース作成と、その確認
sql serverで、日本語のutf-8を扱う場合、
「COLLATE Japanese_XJIS_140_CI_AS_UTF8」を指定するようですので
以下のようにデータベース作成します。
$ sqlcmd -S localhost -U sa -P ????24k! -C -s\| -W
SQL> CREATE DATABASE xserial COLLATE Japanese_XJIS_140_CI_AS_UTF8
go
SQL> SELECT db.name, db.create_date,
pcpl.name as owner, collation_name
FROM sys.databases db
JOIN sys.server_principals pcpl
ON db.owner_sid=pcpl.sid
go
ログインユーザとデータベースユーザの追加
sql serverでは、db接続用の「ログインユーザ」と、
db操作用の「データベースユーザ」の2つが必要ですので、追加します。
まず、ログインユーザの追加と、追加結果の確認
SQL> CREATE LOGIN end0tknr
WITH PASSWORD = 'end0tknr',
CHECK_EXPIRATION= OFF,
CHECK_POLICY = OFF
go
SQL> SELECT name, sid, type_desc,
FORMAT(create_date, 'yyyy-MM-dd') as create_date,
FORMAT(modify_date, 'yyyy-MM-dd') as modify_date
FROM sys.server_principals
WHERE type IN ('S','U')
go
name |sid |type_desc|create_date|modify_date
--------|------------|---------|-----------|-----------
sa |0x01 |SQL_LOGIN|2003-04-08 |2024-04-23
end0tknr|0xC5AE＜略＞|SQL_LOGIN|2024-04-27 |2024-04-27
次に先程作成したデータベース用のユーザ追加と、確認
USE xserial
go
CREATE USER end0tknr FOR LOGIN end0tknr
go
SELECT
name, sid, type_desc,
FORMAT(create_date, 'yyyy-MM-dd') as create_date,
FORMAT(modify_date, 'yyyy-MM-dd') as modify_date
FROM sys.database_principals
WHERE type IN ('S','U')
go
name |sid |type_desc|create_date|modify_date
------------------|-------|---------|-----------|-----------
dbo |0x01 |SQL_USER |2003-04-08 |2024-04-27
guest |0x00 |SQL_USER |2003-04-08 |2003-04-08
INFORMATION_SCHEMA|NULL |SQL_USER |2009-04-13 |2009-04-13
sys |NULL |SQL_USER |2009-04-13 |2009-04-13
end0tknr |0xC5 略|SQL_USER |2024-04-27 |2024-04-27
ロールへのユーザの追加
use xserial
go
ALTER ROLE db_owner ADD MEMBER end0tknr
go
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name
go
DatabaseRoleName |DatabaseUserName
-----------------|----------------
db_accessadmin |No members
db_backupoperator|No members
db_datareader |No members
db_datawriter |No members
db_ddladmin |No members
db_denydatareader|No members
db_denydatawriter|No members
db_owner |dbo
db_owner |end0tknr
db_securityadmin |No members
public |No members
参考url
https://learn.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql
次に sysadmin ロールにも追加します。
sql serverには、bulkadmin というバルクインサート用のロールがありますが、
ドキュメントによれば、
SQL Server on Linux では、ADMINISTER BULK OPERATIONS アクセス許可
または bulkadmin ロールはサポートされていません。
SQL Server on Linux に対して一括挿入を実行できるのは、sysadmin だけです。
https://learn.microsoft.com/ja-jp/sql/t-sql/statements/bulk-insert-transact-sql
のようですので、次の手順でロールにメンバーを追加します。
SQL> use master
go
SQL> ALTER SERVER ROLE sysadmin ADD MEMBER end0tknr
go
SQL> SELECT rp.name, mp.name
FROM sys.server_role_members srm
JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id
WHERE mp.name = 'end0tknr'
go
name |name
--------|--------
sysadmin|end0tknr
インポート先のテーブル作成と確認
SQL> CREATE TABLE dteam_attr (
dteam_code char(3) DEFAULT ' ' NOT NULL,
dteam_name varchar(40) DEFAULT ' ' NOT NULL ,
entry_day char(8) DEFAULT ' ' NOT NULL,
renew_day char(8) DEFAULT ' ' NOT NULL,
constraint dteam_attr_uidx primary key clustered (dteam_code)
)
go
SQL> SELECT name, type, type_desc,
FORMAT(create_date, 'yyyy-MM-dd') as create_date,
FORMAT(modify_date, 'yyyy-MM-dd') as modify_date
FROM sys.objects
WHERE type = 'U'
go
name |type|type_desc |create_date|modify_date
----------|----|----------|-----------|-----------
dteam_attr|U |USER_TABLE|2024-04-27 |2024-04-27
SQL> SELECT
TABLE_CATALOG as CATALOG,
TABLE_SCHEMA as SCHEMA, TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION as POS,
COLUMN_DEFAULT as DETAULT,
IS_NULLABLE as NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH as MAX_LEN
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'dteam_attr'
go
CATALOG|SCHEMA|TABLE_NAME|COLUMN_NAME|POS|DEFAULT|NULLABLE|DATA_TYPE|MAX_LEN
-------|------|----------|-----------|---|-------|--------|---------|-------
xserial|dbo |dteam_attr|dteam_code |1 |(' ') |NO |char |3
xserial|dbo |dteam_attr|dteam_name |2 |(' ') |NO |varchar |40
xserial|dbo |dteam_attr|entry_day |3 |(' ') |NO |char |8
xserial|dbo |dteam_attr|renew_day |4 |(' ') |NO |char |8
SQL> SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'dteam_attr'
go
CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|TABLE_CATALOG|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME
------------------|-----------------|---------------|-------------|------------|----------|-----------
xserial |dbo |dteam_attr_uidx|xserial |dbo |dteam_attr|dteam_code
SQL> SELECT name, collation_name FROM sys.columns WHERE name = 'dteam_name'
name |collation_name
----------|-----------------------------
dteam_name|Japanese_XJIS_140_CI_AS_UTF8
/opt/mssql/lib/mssql-conf/mssqlsettingsmanager.py の編集
この後、実行する bcp コマンドで、sslのエラーになったので、
以下のように FORCE_ENCRYPTION を Falseに。
(更に後から実行すると、sslエラーにならなかったので、実は不要だったのかも)
supportedSettingsList.append(mssqlsettings.BooleanSetting("forceencryption",
"MSSQL_FORCE_ENCRYPTION",
mssqlsettings.SettingValueType.boolean,
_("Force encryption of incoming client connections"),
mssqlsettings.SectionForSetting.network,
False,
"1",
"0"))
上記の編集後、sql server再起動
$ sudo systemctl restart mssql-server
$ bcp dteam_attr in /home/end0tknr/tmp/BCP/dteam_attr.bcp
-S localhost -d xserial -U end0tknr -P end0tknr -q -c -t "," -u
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation
28 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 30 Average : (933.3 rows per sec.)
インポート結果の確認
$ sqlcmd -S localhost -U end0tknr -P end0tknr -C -s\| -W
1> use xserial
2> go
Changed database context to 'xserial'.
1> select * from dteam_attr
2> go
dteam_code|dteam_name|entry_day|renew_day
----------|----------|---------|---------
ele |電気 |19910528 |20240227
＜略＞
(28 rows affected)
