Visual Studio の Database Project で Temporal Table 使ってると公開が失敗することがある

列の型を変えたり、Temporal Table が要らなくなって、定義から消した場合*1にやられる。。
Cannot change data types of a temporal table columns - Developer Community

回避方法は ↑ に書いてある通り、*.dbmdl 消したり、↓ のような alter で Temporal Table を止めたりしないとダメ。

alter table [HogeTable] set (system_versioning = off)

列の型変えるでエラーを防ごうとした場合、History テーブルもちゃんと CREATE 文で作ってやったらよさそう。

失敗する例：History テーブルを自動で生成しているため、列の型を変更すると失敗する

CREATE TABLE [dbo].[Table1]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(20) NOT NULL,
    [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[Table1_HISTORY], DATA_CONSISTENCY_CHECK=ON))

成功する例：History テーブルを手動で生成しているので、メイン、ヒストリーテーブル両方の列の型を変更すると成功する

create table [Table1History] (
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(20) NOT NULL,
    [SysStart] DATETIME2 (7) NOT NULL,
    [SysEnd] DATETIME2 (7) NOT NULL
)
GO
CREATE CLUSTERED INDEX [IX_Table1History] ON [Table1History] ([SysEnd], [SysStart])
GO
create table [Table1] (
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(20) NOT NULL,
    [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[Table1History], DATA_CONSISTENCY_CHECK=ON))
GO

なんでか分からないけど、alter 文で system_versioning の設定しようとするとコンパイルエラーになるので、create 時に設定する必要あり。

Temporal Table 周りはまだ色々動き怪しい。。

定義から消したときに、削除するやつは Database Project の中での対処方法は無さそう。
手動 alter で system_versioning = off してください。

*1:ソースになくて、ターゲットにある場合に削除する

メモ：存在しない行を HOLDLOCK すると、Range でロックする

たまたま試す機会があったのでメモ。

テーブル ヒント (Transact-SQL) - SQL Server | Microsoft Docs
には HOLDLOCK は SERIALIZABLE 相当と書かれていて、
SERIALIZABLE では、

SERIALIZABLE 分離レベルで実行しているトランザクションと同じセマンティクスで、スキャンが実行されます。

となっているので、ドキュメント通りなんだけど試したから書いとく。

例１：
存在しない行に UPDLOCK を掛けても、ロックは取られない。
なので、他のトランザクションで行を追加されてしまう。

ロック掛けるセッション

begin tran

select * from [Table] (UPDLOCK) where Id = 6

追加するセッション

begin tran
insert into [Table] values (6, 'abcabc')
select * from [Table]
rollback

Id = 6 が追加される。

例２：
HOLDLOCK にすると、行を追加されない。

ロック掛けるセッション

begin tran

select * from [Table] (HOLDLOCK) where Id = 6

追加するセッション

begin tran
insert into [Table] values (6, 'abcabc')
select * from [Table]
rollback

ロックが解放されるまで追加できない。

ロック状況

範囲でロックを取っているので、今回の場合は Id = 6 だけではなく、6 以上の値では insert 出来ない。
逆に Id = 0 や -1 は insert 可能。

Id が 1 と 5 のデータしかない場合に、Id = 3 で HOLDLOCK 掛けたら、1 より小さい値や、5 より大きい値はロックの範囲外。
2, 3, 4 はロックの範囲内。
多分こんな認識で大丈夫のはず。

DACFX の .NET Core 版がぼちぼち出る？

DacFx .NET Core Support · Issue #20 · Microsoft/DACExtensions · GitHub

試してませんが preview あるようです。
NuGet Gallery | Microsoft.SqlServer.DACFx 150.4240.1-preview

SQL Server 2019 から sparkjava を実行してみた

SQL Server 2019 CTP2 で Java を動かしてみた - お だ のスペース で紹介した external_script で Java を呼べる機能を使って、Spark Framework: An expressive web framework for Kotlin and Java を実行してみました。
meetup app osaka@3 で Spark on SQL Server? という話ししました #meetupapp - お だ のスペース のスライドでも書いてますが、動いてますが外部*1から HTTP アクセス出来ませんでした。
なので、同じメソッド内から OkHttp で GETアクセスして動作確認しています。

Java 久しぶりすぎて、Maven も Gradle も書かれへんかった。。
適当に拾ってきたものをいじって、

• com.sparkjava.spark-core
• com.squareup.okhttp3.okhttp

を依存関係に突っ込んでるだけです。
幾つか必要な static フィールドがありますが、 How to call Java from SQL - SQL Server Machine Learning Services - SQL Server | Microsoft Docs を見てください。
実装の中で使わなくても今回のケースでは問題無いです。*2

package pkg;

import static spark.Spark.*;
import java.io.IOException;
import java.time.LocalDateTime;

import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;

public class Hoge {
    public static int[] inputDataCol1 = new int[1];
    public static String[] inputDataCol2 = new String[1];

    //Required: Input null map. Size just needs to be set to "1"
    public static boolean[][] inputNullMap = new boolean[1][1];

    //Required: Output data columns returned back to SQL Server
    public static int[] outputDataCol1;
    public static String[] outputDataCol2;

    //Required: Output null map. Is populated with true or false values 
    //to indicate nulls
    public static boolean[][] outputNullMap;

    //Optional: This is only required if parameters are passed with @params
    // from SQL Server in sp_execute_external_script
    // n is giving us the size of ngram substrings
    public static int param1;

    //Optional: The number of rows we will be returning
    public static int numberOfRows;

    //Required: Number of output columns returned
    public static short numberOfOutputCols;

    public static void runSqlServer() {
        get("/hello", (request, response) -> "<h1>Hello Spark!! on SQL Server?</h1>");
        try { 
            String res = run("http://localhost:4567/hello");
            System.out.println(res);
        } catch (IOException e) {
            System.out.println(e);
        }
    }
    static OkHttpClient client = new OkHttpClient();

    static String run(String url) throws IOException {
        Request request = new Request.Builder()
            .url(url)
            .build();

        try (Response response = client.newCall(request).execute()) {
            return response.body().string();
        }
    }
}

これで HTTP アクセスが失敗する場合は、spark の設定 と HTTP アクセスの間に Thread.sleep なりなんなりで処理を遅らせてください。
Thread.sleep 入れて、実行中にポートの待ち受けを確認するとこんな感じです。
スライドにも書いてますが、Java.exe ではなく、ExtHost.exe として動いています。
ExtHost.exe として、4567 ポートで待ち受けています。
メソッド内の処理が全て終わってしまう*3と、spark のサーバーは落ちてしまいます。
外部からアクセス出来ないし、ずっと動かすには sleep や無限ループ等でメソッドが終わらないようにする必要があります。
※ExtHost にタイムアウトがあるかは未検証なので、無限ループの場合は注意
という訳で、余り実用向けではありませんが遊ぶ分には問題ありません。

sparkjava の実行とは関係無いですが、OkHttp なりなんなりで、外部にHTTPアクセスする事も出来ます。
なので外部のAPIとかを叩いてデータを引っ張ってきて遊ぶことも出来ると思います。

今回ご紹介したものは、「実運用を目指していない」お遊びのものなので、本番でこの構成を使おうとか間違っても辞めてね。。

*1:ローカルからの別プロセスも

*2:普通に使うと Java へのパラメーターや戻り値といった使い方になります

*3:external_script の呼出が終わってしまう

meetup app osaka@3 で Spark on SQL Server? という話ししました #meetupapp

なんか色々トラブった*1けど、無事やりきりました。

ざっくり解説すると、
SQL Server 2019 で Big Data Cluster が出て、Spark ジョブも叩けるよーからの、
SQL Server 2019 で追加された Java language extension in SQL Server 2019 - SQL Server Machine Learning Services - SQL Server | Microsoft Docs で Spark Framework: An expressive web framework for Kotlin and Java を動かすっていう話しです。
ExtHost.exe(SQL Server External Host) 上で動くから、on って言える？？位のネタ話し。

あとは Java から HttpClient で 外部のAPI 叩けるから、どこからでもデータ取れるよー。*2
みたいな感じ。

スライドはこちら

Spark on sql server? from Oda Shinsuke

*1:HDMIで繋がらない + PCのタッチパッドが無効になってマウス効かない

*2:使う場合は Firewall の設定は要確認

12/22(土) meetup app osaka@3 で SQL Server 2019 の話しします

今週土曜12/22 に
meetup app osaka@3 - connpass
で SQL Server 2019 の話しします。

といっても 15分程度なのでサラッと話す感じですが、内容は「えっ？」ってなるような物を用意しています。
人を選びそうなネタになっていますが、DB系の勉強会では無いので有りじゃないかな？*1

興味ある人はぜひご参加を～。

*1:カテゴリー の Java は話す内容のヒントです

SQL Server 2019 CTP2 で Java を動かしてみた

SQL Server 2019 から external_script で Java が呼べるようになりました。
Java language extension in SQL Server 2019 | Microsoft Docs
How to call Java from SQL | Microsoft Docs
Java language extension in SQL Server 2019 | Microsoft Docs
Java data types supported in SQL Server 2019 | Microsoft Docs

Windows の場合は、JDK1.10 ってありましたが、OracleJDK 11.0.1 でも OK でした。

環境作るの面倒だったので、Azure の VM で Win の SQL Server 2019 CTP2 入りのやつ使いました。
手順通り進めたらサクッと動く感じです。
ハマりどころは LaunchPad サービスを起動してないとダメとか位？

external_script では、2016 で R、2017 で Python が追加されましたが、
実行方法は、下の Execution architecture の箇所に書いてます。
Python extension in SQL Server Machine Learning Services | Microsoft Docs
R extension in SQL Server | Microsoft Docs

要は SQL Server と別プロセスで動く形です。
Python だと Python.exe が動いたので、Java の場合は Java.exe になるのかなーと動かしてみましたがパッと見は分からず。
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\ExtHost.exe(SQL External Runtime Host) ってのが動いてる感じでしたが、これ経由で Java 動かしてるのかな？