using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;public class StorageIntegrationConfig
{public string Name { get; set; }public bool IfNotExists { get; set; }public string Type { get; set; }public bool Enabled { get; set; }public string StorageProvider { get; set; }public string StorageAwsRoleArn { get; set; }public string StorageAwsObjectAcl { get; set; }public List<string> AllowedLocations { get; set; }public List<string> BlockedLocations { get; set; }public string Comment { get; set; }
}public static class StorageIntegrationSqlGenerator
{public static string Generate(StorageIntegrationConfig config){var sb = new StringBuilder();// 基本结构sb.Append("CREATE STORAGE INTEGRATION ");if (config.IfNotExists) sb.Append("IF NOT EXISTS ");sb.AppendLine(config.Name);// 必要参数AppendIfNotNull(sb, "TYPE", config.Type);sb.AppendLine($" ENABLED = {config.Enabled.ToString().ToUpper()}");sb.AppendLine($" STORAGE_PROVIDER = '{EscapeSingleQuote(config.StorageProvider)}'");// AWS相关参数AppendIfNotNull(sb, "STORAGE_AWS_ROLE_ARN", config.StorageAwsRoleArn, true);AppendIfNotNull(sb, "STORAGE_AWS_OBJECT_ACL", config.StorageAwsObjectAcl, true);// 路径配置AppendLocationList(sb, "STORAGE_ALLOWED_LOCATIONS", config.AllowedLocations);AppendLocationList(sb, "STORAGE_BLOCKED_LOCATIONS", config.BlockedLocations);// 注释AppendIfNotNull(sb, "COMMENT", config.Comment, true);return sb.ToString().TrimEnd();}private static void AppendIfNotNull(StringBuilder sb, string name, string value, bool quote = false){if (!string.IsNullOrWhiteSpace(value)){var formattedValue = quote ? $"'{EscapeSingleQuote(value)}'" : value;sb.AppendLine($" {name} = {formattedValue}");}}private static void AppendLocationList(StringBuilder sb, string name, List<string> locations){if (locations?.Any() == true){var formatted = string.Join(", ", locations.Select(l => $"'{EscapeSingleQuote(l)}'"));sb.AppendLine($" {name} = ({formatted})");}}private static string EscapeSingleQuote(string input) => input?.Replace("'", "''") ?? string.Empty;
}// 使用示例
public class Program
{public static void Main(){var config = new StorageIntegrationConfig{Name = "my_s3_int",IfNotExists = true,Type = "EXTERNAL_STAGE",Enabled = true,StorageProvider = "S3",StorageAwsRoleArn = "arn:aws:iam::001234567890:role/my-role",AllowedLocations = new List<string> { "s3://my-bucket/path1/", "s3://my-bucket/path2/" },Comment = "Integration with S3 storage"};var sql = StorageIntegrationSqlGenerator.Generate(config);Console.WriteLine(sql);}
}
输出结果示例:
CREATE STORAGE INTEGRATION IF NOT EXISTS my_s3_intTYPE = EXTERNAL_STAGEENABLED = TRUESTORAGE_PROVIDER = 'S3'STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/my-role'STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/path1/', 's3://my-bucket/path2/')COMMENT = 'Integration with S3 storage'
代码特点:
- 使用StringBuilder高效构建SQL语句
- 自动处理:
- 单引号转义
- 列表格式转换
- 可选参数处理
- 大小写转换(BOOL类型)
- 支持所有CREATE STORAGE INTEGRATION参数
- 符合Snowflake SQL语法规范
- 清晰的扩展结构,方便新增参数支持