end0tknr's kipple - web写経開発

太宰府天満宮の狛犬って、妙にカワイイ

bcp import from utf-8 csv to SQL Server 2022 for Linux

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,
#        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)