using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Data.Odbc;
using Microsoft.SqlServer.Server;
using Newtonsoft.Json; // Newtonsoft.Json 殊企襴 豢螳
using System.Data;
using System.Text;
using System.Transactions;
public class TrinoODBC
{
// 襷り覲襦 貎朱Μ襷 覦, DSN 貊 伎 貊
[SqlFunction(
FillRowMethodName = "FillRow",
TableDefinition = "json_data NVARCHAR(MAX)",
DataAccess = DataAccessKind.Read, // 一危磯ゼ 曙 襦 れ
SystemDataAccess = SystemDataAccessKind.Read // ろ 一危一 蠏狩 襦 れ
)]
public static IEnumerable ExecuteTrinoQuery(SqlString query, SqlString parameters, SqlString dsn)
{
return ExecuteQuery(query.Value, parameters.Value, dsn.Value);
}
// 貎朱Μ襯 襷り覲襦 覦 ろ 覃
private static IEnumerable ExecuteQuery(string sql, string parameters, string dsn)
{
List<SqlString> resultData = new List<SqlString>();
string query = "";
// JSON 朱 朱誤磯ゼ
var paramDict = string.IsNullOrEmpty(parameters) || parameters == "{}"
? new Dictionary<string, string>()
: JsonConvert.DeserializeObject<Dictionary<string, string>>(parameters);
// 朱誤 豺
query = sql;
foreach (var param in paramDict)
{
query = query.Replace(param.Key, param.Value);
}
using (var transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
{
// ODBC 郁屋 覓語 れ (Trino DSN 貊)
string connectionString = "DSN=" + dsn; // TrinoDSN 貊朱 れ
// ODBC 郁屋 Trino 貎朱Μ ろ
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
connection.Open();
OdbcCommand command = new OdbcCommand(query, connection);
OdbcDataReader reader = command.ExecuteReader();
// 蟆郁骸襯 曙 覈 貉殊 JSON 朱 覦
while (reader.Read())
{
// 螳 Dictionary襦 (貉朱螻 螳 )
var rowValues = new Dictionary<string, object>();
// 貉朱螻 螳 Dictionary 豢螳
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
object value = reader.GetValue(i);
// DateTime 豌襴: DateTime 企 讌
if (value is DateTime)
{
DateTime dtValue = (DateTime)value;
// 螳 00:00:00.000企 讌襷 覦
if (dtValue.TimeOfDay == TimeSpan.Zero)
{
value = dtValue.ToString("yyyy-MM-dd");
}
else
{
value = dtValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
}
rowValues[columnName] = value;
}
// Dictionary襯 JSON朱 讌
string jsonRow = JsonConvert.SerializeObject(rowValues);
// JSON 讌 UTF-8 覲
//byte[] utf8Bytes = Encoding.UTF8.GetBytes(jsonRow);
//string utf8String = Encoding.UTF8.GetString(utf8Bytes); // UTF-8襦 覲
// 蟆郁骸 豢螳
resultData.Add(new SqlString(jsonRow));
//resultData.Add(new SqlString(utf8String));
}
}
return resultData;
}
}
// 螳 SQL 一危磯 豈磯 覃
public static void FillRow(object obj, out SqlString jsonData)
{
jsonData = new SqlString(obj.ToString());
}
}
drop function dbo.trino
drop function dbo.odbc_query
drop ASSEMBLY TrinoCLR
go
CREATE ASSEMBLY odbc_query
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300F47FAE670000000000000000E00002210B010800000E000000060000000000003E2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000EC2C00004F00000000400000B002000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000440D000000200000000E000000020000000000000000000000000000200000602E72737263000000B0020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000202D0000000000004800000002000500B02200003C0A00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003002000000001000011000F00280500000A0F01280500000A0F02280500000A28020000060A2B00062A1B300300C00100000200001100730600000A0A72010000700B03280700000A2D15037203000070280800000A2D0803280100002B2B05730A00000A0C020B00086F0B00000A13102B1F1210280C00000A0D00071203280D00000A1203280E00000A6F0F00000A0B001210281000000A131111112DD4DE0F1210FE160300001B6F1100000A00DC0018731200000A130400720900007004281300000A13051105731400000A13060011066F1500000A00071106731600000A130711076F1700000A130838C000000000731800000A130916130A3884000000001108110A6F1900000A130B1108110A6F1A00000A130C110C751600000114FE0316FE01131111112D4700110CA516000001130D120D281B00000A7E1C00000A281D00000A16FE01131111112D1200120D7213000070281E00000A130C002B1000120D7229000070281E00000A130C00001109110B110C6F1F00000A0000110A1758130A110A11086F2000000AFE04131111113A68FFFFFF1109282100000A130E06110E732200000A6F2300000A000011086F2400000A131111113A30FFFFFF00DE14110614FE01131111112D0811066F1100000A00DC0006130FDE14110414FE01131111112D0811046F1100000A00DC00110F2A414C0000020000003B000000300000006B0000000F00000000000000020000009A000000F40000008E0100001400000000000000020000008300000025010000A801000014000000000000004E0003026F2500000A732200000A81030000012A1E02282600000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D0020000237E00003C030000CC03000023537472696E677300000000080700005C000000235553006407000010000000234755494400000074070000C802000023426C6F620000000000000002000001471502080908000000FA01330016000001000000170000000200000004000000080000002600000003000000020000000500000001000000040000000100000000000A00010000000000060034002D0006004E003B000A007B0066000600F700D8000600240104010600440104010A0089016E010600C301A8010600CA012D000E001102010206002F02A8012F003C02000006005502A801060089022D001200B1029D021200C2029D020A00F102E0020A00130300030A002503E0020A003103E0020A004E03000306006C032D00060075032D000000000001000000000001000100010010001A000000050001000100502000000000960085000A0001007C200000000091009700150004009422000000009600A4001D000700A822000000008618AC002500090000000100B20000000200B80000000300C30000000100C70000000200B80000000300C30000000100CB0002000200CF002100AC0025002900AC0029003100AC0025003900AC00250019009E019C010C00AC0025004900D101AC014900DF01B10151001D02C0011400AC00250014004702D7011C006402E90124007002FB0124009E0100024900780205021C0080020B027100950225007900AC000F024900D90215028900AC001B029100200325009900AC0020029900400327022C00AC002500A9005B033302A90063033802B1007E033D02B9008C034202B900DF014602B10091034E022C009A035302A900A3035B025100B2035F021900AC001B020C00C2036402A900C6030B02090091039C010900AC002500200023002E002E0013009E022E001B00A702A0016A02A501D001E201F4012C0204800000000000000000000000000000000062010000020000000000000000000000010024000000000002000000000000000000000001005A0000000000030005000000000000000000B701EB010000000002000000000000000000000001009D02000000001300C7010000003C4D6F64756C653E007472696E6F5F71756572792E646C6C005472696E6F4F444243006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E6700457865637574655472696E6F5175657279004578656375746551756572790046696C6C526F77002E63746F7200717565727900706172616D65746572730064736E0073716C006F626A006A736F6E446174610053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007472696E6F5F7175657279004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75650053797374656D2E436F6C6C656374696F6E732E47656E65726963004C697374603100537472696E670049734E756C6C4F72456D707479006F705F457175616C697479004E6577746F6E736F66742E4A736F6E2E4E65743230004E6577746F6E736F66742E4A736F6E004A736F6E436F6E7665727400446573657269616C697A654F626A6563740044696374696F6E617279603200456E756D657261746F7200476574456E756D657261746F72004B657956616C7565506169726032006765745F43757272656E74006765745F4B6579005265706C616365004D6F76654E6578740049446973706F7361626C6500446973706F73650053797374656D2E5472616E73616374696F6E73005472616E73616374696F6E53636F7065005472616E73616374696F6E53636F70654F7074696F6E00436F6E6361740053797374656D2E446174612E4F646263004F646263436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E004F646263436F6D6D616E64004F64626344617461526561646572004578656375746552656164657200446244617461526561646572004765744E616D650047657456616C7565004461746554696D650054696D655370616E006765745F54696D654F66446179005A65726F00546F537472696E67007365745F4974656D006765745F4669656C64436F756E740053657269616C697A654F626A6563740041646400526561640000000100057B007D000009440053004E003D00001579007900790079002D004D004D002D0064006400012F79007900790079002D004D004D002D00640064002000480048003A006D006D003A00730073002E0066006600660001000000614CE50C3B32E24A9730B9B9FDCA74CD0008B77A5C561934E0890A00031209110D110D110D07000312090E0E0E070002011C10110D032000010420010108816C01000400540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E176A736F6E5F64617461204E56415243484152284D4158295455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D44617461416363657373010000000320000E04070112090615122101110D040001020E050002020E0E0830AD4FE6B2A6AEED061001011E000E080A0115122D020E0E0615122D020E0E0A2000151131021300130106151131020E0E0A2000151135021300130106151135020E0E042000130004200013010520020E0E0E032000020520010111410500020E0E0E042001010E062002010E124504200012510615122D020E1C0420010E080420011C08042000115D0306115D07000202115D115D0420010E0E0720020113001301032000080400010E1C05200101130033071215122101110D0E15122D020E0E151135020E0E123D0E1245124D125115122D020E1C080E1C11590E1209151131020E0E020801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000142D000000000000000000002E2D0000002000000000000000000000000000000000000000000000202D0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000540200000000000000000000540234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004B4010000010053007400720069006E006700460069006C00650049006E0066006F0000009001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D00650000007400720069006E006F005F00710075006500720079002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000007400720069006E006F005F00710075006500720079002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000403D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE
GO
--
CREATE FUNCTION dbo.odbc_query(@query NVARCHAR(MAX), @params nvarchar(4000), @dsn nvarchar(100) = 'trino')
RETURNS TABLE(json_data NVARCHAR(MAX))
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME odbc_query.[TrinoODBC].ExecuteTrinoQuery;
GO
--
CREATE FUNCTION dbo.trino(@query NVARCHAR(MAX), @params nvarchar(4000))
RETURNS TABLE
AS
return(select * from dw.dbo.odbc_query(@query, @params, 'trino'))
declare
@start_date date = '20250101'
, @end_date date = '20250110'
declare
@param nvarchar(4000)
select @param =
(
select
convert(char(8), @start_date, 112) [@start_date]
, convert(char(8), @end_date, 112) [@end_date]
for json path, without_array_wrapper
)
print @param
--drop table if exists #temp
select * --into #temp
from dw.dbo.trino('
select
date_key
, count(*) cnt
from fact_xxxx
where date_key between @start_date and @end_date
group by
date_key
', @param)