install SQL Server to Oracle Linux 8 - end0tknr's kipple - web写経開発
先日の上記entryの続きです。
今回は、SQL Server 2022 for Linux に対し、 utf-8で記載されたcsvファイルをbcpコマンドでインポートします。
目次
- utf-8のデータベース作成と、その確認
- ログインユーザとデータベースユーザの追加
- ロールへのユーザの追加
- インポート先のテーブル作成と確認
- /opt/mssql/lib/mssql-conf/mssqlsettingsmanager.py の編集
- 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
次に 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, # True, "1", # true value "0")) # false value
上記の編集後、sql server再起動
$ sudo systemctl restart mssql-server
utf-8で記載されたcsvファイルをbcpコマンドでインポート
$ 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)
