{"id":3656,"date":"2025-03-01T13:13:07","date_gmt":"2025-03-01T04:13:07","guid":{"rendered":"https:\/\/blue-bear.jp\/kb\/?p=3656"},"modified":"2025-03-01T13:13:08","modified_gmt":"2025-03-01T04:13:08","slug":"snowflake%e5%86%85%e9%83%a8%e3%82%b9%e3%83%86%e3%83%bc%e3%82%b8%e3%81%8b%e3%82%89ssl%e8%a8%bc%e6%98%8e%e6%9b%b8%e3%82%92%e5%8f%96%e5%be%97%e3%81%97%e3%80%81mysql%ef%bc%88aws-rds%ef%bc%89%e3%81%ab","status":"publish","type":"post","link":"https:\/\/blue-bear.jp\/kb\/snowflake%e5%86%85%e9%83%a8%e3%82%b9%e3%83%86%e3%83%bc%e3%82%b8%e3%81%8b%e3%82%89ssl%e8%a8%bc%e6%98%8e%e6%9b%b8%e3%82%92%e5%8f%96%e5%be%97%e3%81%97%e3%80%81mysql%ef%bc%88aws-rds%ef%bc%89%e3%81%ab\/","title":{"rendered":"Snowflake\u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u304b\u3089SSL\u8a3c\u660e\u66f8\u3092\u53d6\u5f97\u3057\u3001MySQL\uff08AWS RDS\uff09\u306b\u63a5\u7d9a\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"\n

\u3053\u3093\u306b\u3061\u306f\uff01\u4eca\u56de\u306f Snowflake<\/strong> \u306e\u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u306b\u4fdd\u5b58\u3055\u308c\u305f PEM\u30d5\u30a1\u30a4\u30eb\uff08SSL\u8a3c\u660e\u66f8\uff09<\/strong> \u3092\u6d3b\u7528\u3057\u3001AWS RDS (MySQL) \u306b\u5b89\u5168\u306b\u63a5\u7d9a\u3059\u308b\u65b9\u6cd5<\/strong> \u3092\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n\n\n\n

Snowflake \u306e UDF\uff08User Defined Function\uff09\u3092\u6d3b\u7528\u3057\u3066 PEM\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u307f<\/strong>\u3001MySQL \u306e SSL\u63a5\u7d9a<\/strong> \u306b\u5229\u7528\u3059\u308b\u65b9\u6cd5\u3092\u9806\u3092\u8ffd\u3063\u3066\u8aac\u660e\u3057\u307e\u3059\u3002\u5b9f\u969b\u306b\u8a66\u3057\u305f\u30a8\u30e9\u30fc\u3068\u305d\u306e\u89e3\u6c7a\u65b9\u6cd5\u3082\u542b\u3081\u3066\u3044\u308b\u306e\u3067\u3001\u540c\u3058\u8ab2\u984c\u306b\u76f4\u9762\u3057\u3066\u3044\u308b\u65b9\u306e\u53c2\u8003\u306b\u306a\u308c\u3070\u5e78\u3044\u3067\u3059\uff01<\/p>\n\n\n\n

\n\n\n\n

\u30b9\u30c6\u30c3\u30d71: Snowflake \u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u306bSSL\u8a3c\u660e\u66f8\u3092\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9<\/strong><\/h2>\n\n\n\n

\u307e\u305a\u3001SSL\u8a3c\u660e\u66f8\uff08PEM\u30d5\u30a1\u30a4\u30eb\uff09\u3092 Snowflake \u306e \u5185\u90e8\u30b9\u30c6\u30fc\u30b8<\/strong> \u306b\u4fdd\u5b58\u3057\u307e\u3059\u3002\u3053\u308c\u306f\u3001\u5f8c\u306e UDF \u3067\u4f7f\u7528\u3059\u308b\u305f\u3081\u306e\u6e96\u5099\u3067\u3059\u3002<\/p>\n\n\n\n

1.1 \u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u3092\u4f5c\u6210<\/strong><\/h3>\n\n\n\n
CREATE OR REPLACE STAGE my_stage;\n<\/code><\/pre>\n\n\n\n
1.2 \u30ed\u30fc\u30ab\u30eb\u306e SSL \u8a3c\u660e\u66f8\u3092\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9<\/strong><\/h3>\n\n\n\n
PUT file:\/\/path\/to\/your\/ca-bundle.pem @my_stage AUTO_COMPRESS=FALSE;\n<\/code><\/pre>\n\n\n\n
1.3 \u30b9\u30c6\u30fc\u30b8\u5185\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u78ba\u8a8d<\/strong><\/h3>\n\n\n\n
LIST @my_stage;\n<\/code><\/pre>\n\n\n\n
\u3053\u308c\u3067\u3001\u8a3c\u660e\u66f8\u30d5\u30a1\u30a4\u30eb\u304c Snowflake \u306e\u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u306b\u683c\u7d0d\u3055\u308c\u307e\u3057\u305f\u3002\u6b21\u306b\u3001\u3053\u306e\u8a3c\u660e\u66f8\u3092 Python UDF \u3067\u8aad\u307f\u8fbc\u3080\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
\n\n\n\n
\u30b9\u30c6\u30c3\u30d72: Snowflake UDF \u3067 SSL \u8a3c\u660e\u66f8\u3092\u53d6\u5f97<\/strong><\/h2>\n\n\n\n
Snowflake \u306e Python UDF<\/strong> \u3092\u4f5c\u6210\u3057\u3001\u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u304b\u3089 PEM\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3080<\/strong> \u95a2\u6570\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
CREATE OR REPLACE FUNCTION read_pem_file()\nRETURNS STRING\nLANGUAGE PYTHON\nRUNTIME_VERSION = '3.8'\nHANDLER = 'read_file'\nAS $$\nimport _snowflake\n\ndef read_file():\n    # \u30b9\u30c6\u30fc\u30b8\u4e0a\u306e\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\u3092\u6307\u5b9a\n    file_path = \"@my_stage\/ca-bundle.pem\"\n\n    try:\n        # Snowflake\u306e\u30b9\u30c6\u30fc\u30b8\u304b\u3089\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u307f\n        with _snowflake.open(file_path, 'r', require_scoped_url=False) as f:\n            content = f.read()\n        return content\n    except Exception as e:\n        return f\"Error reading file: {str(e)}\"\n$$;\n<\/code><\/pre>\n\n\n\n
\u3053\u306e\u95a2\u6570\u3092\u547c\u3073\u51fa\u305b\u3070\u3001\u30b9\u30c6\u30fc\u30b8\u5185\u306e\u8a3c\u660e\u66f8\u30d5\u30a1\u30a4\u30eb\u306e\u5185\u5bb9\u3092\u53d6\u5f97\u3067\u304d\u307e\u3059\u3002<\/p>\n\n\n\n
SELECT read_pem_file();\n<\/code><\/pre>\n\n\n\n
✅ \u30dd\u30a4\u30f3\u30c8<\/strong><\/p>\n\n\n\n
    \n
  • _snowflake.open()<\/code> \u3092\u4f7f\u3063\u3066\u3001\u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u306e\u30d5\u30a1\u30a4\u30eb\u3092 Python UDF \u3067\u8aad\u307f\u8fbc\u3080\u3002<\/li>\n\n\n\n
  • require_scoped_url=False<\/code> \u3092\u6307\u5b9a\u3057\u3066\u3001\u30b9\u30b3\u30fc\u30d7\u4ed8\u304dURL\u306e\u30c1\u30a7\u30c3\u30af\u3092\u56de\u907f\u3059\u308b\u3002<\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n
    \u30b9\u30c6\u30c3\u30d73: Snowflake \u306e UDF \u3092\u6d3b\u7528\u3057\u3066 MySQL (AWS RDS) \u306b\u63a5\u7d9a<\/strong><\/h2>\n\n\n\n
    \u6b21\u306b\u3001\u53d6\u5f97\u3057\u305f SSL \u8a3c\u660e\u66f8\u3092\u4f7f\u3063\u3066 AWS RDS (MySQL) \u306b\u5b89\u5168\u306b\u63a5\u7d9a<\/strong> \u3059\u308b\u65b9\u6cd5\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
    3.1 AWS RDS \u306e IAM \u8a8d\u8a3c\u30c8\u30fc\u30af\u30f3\u3092\u751f\u6210<\/strong><\/h3>\n\n\n\n
    AWS RDS \u306b\u63a5\u7d9a\u3059\u308b\u969b\u3001IAM \u8a8d\u8a3c\u3092\u5229\u7528\u3057\u3066\u30bb\u30ad\u30e5\u30ea\u30c6\u30a3\u3092\u5f37\u5316\u3057\u307e\u3059\u3002boto3<\/code> \u3092\u4f7f\u3063\u3066\u30c8\u30fc\u30af\u30f3\u3092\u751f\u6210\u3057\u3001MySQL \u306e\u30d1\u30b9\u30ef\u30fc\u30c9\u3068\u3057\u3066\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
    3.2 MySQL \u306b\u63a5\u7d9a\u3059\u308b Snowflake UDF \u306e\u4f5c\u6210<\/strong><\/h3>\n\n\n\n
    CREATE OR REPLACE FUNCTION connect_rds()\nRETURNS STRING\nLANGUAGE PYTHON\nRUNTIME_VERSION = '3.8'\nPACKAGES = ('boto3', 'pymysql')\nHANDLER = 'main'\nAS $$\nimport _snowflake\nimport boto3\nimport pymysql\nimport tempfile\n\n# RDS\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u60c5\u5831\nrds_host = \"your-rds-endpoint\"\nusername = \"jane_doe\"\ndb_name = \"your-database-name\"\nregion = \"your-region\"\n\ndef main():\n    try:\n        # Snowflake\u5185\u90e8\u30b9\u30c6\u30fc\u30b8\u304b\u3089SSL\u8a3c\u660e\u66f8\u3092\u53d6\u5f97\n        ssl_cert_stage_path = \"@my_stage\/ca-bundle.pem\"\n        with _snowflake.open(ssl_cert_stage_path, 'r', require_scoped_url=False) as cert_file:\n            ssl_cert_content = cert_file.read()\n        \n        # AWS IAM\u8a8d\u8a3c\u30c8\u30fc\u30af\u30f3\u3092\u751f\u6210\n        boto_session = boto3.Session(region_name=region)\n        rds_client = boto_session.client('rds')\n        token = rds_client.generate_db_auth_token(DBHostname=rds_host, Port=3306, DBUsername=username)\n        \n        # \u8a3c\u660e\u66f8\u3092\u4e00\u6642\u30d5\u30a1\u30a4\u30eb\u306b\u4fdd\u5b58\n        with tempfile.NamedTemporaryFile(mode='w', delete=False, suffix=\".pem\") as tmp_cert_file:\n            tmp_cert_file.write(ssl_cert_content)\n            tmp_cert_path = tmp_cert_file.name\n        \n        # MySQL \u306b\u63a5\u7d9a\n        conn = pymysql.connect(\n            host=rds_host,\n            user=username,\n            password=token,\n            database=db_name,\n            ssl_ca=tmp_cert_path,  # SSL\u8a3c\u660e\u66f8\u3092\u4f7f\u7528\n            port=3306\n        )\n        return \"Successfully connected to RDS with SSL!\"\n    \n    except Exception as e:\n        return f\"Error: {str(e)}\"\n$$;\n<\/code><\/pre>\n\n\n\n
    3.3 UDF \u3092\u5b9f\u884c<\/strong><\/h3>\n\n\n\n
    SELECT connect_rds();\n<\/code><\/pre>\n\n\n\n
    ✅ \u30dd\u30a4\u30f3\u30c8<\/strong><\/p>\n\n\n\n
      \n
    1. \u30b9\u30c6\u30fc\u30b8\u304b\u3089\u8a3c\u660e\u66f8\u3092\u53d6\u5f97<\/strong>\n
        \n
      • _snowflake.open()<\/code> \u3092\u4f7f\u3044\u3001SSL \u8a3c\u660e\u66f8\u306e\u5185\u5bb9\u3092\u53d6\u5f97\u3002<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • AWS IAM \u8a8d\u8a3c\u30c8\u30fc\u30af\u30f3\u3092\u751f\u6210<\/strong>\n
          \n
        • boto3.client('rds')<\/code> \u3092\u4f7f\u7528\u3057\u3001MySQL \u306e\u8a8d\u8a3c\u306b\u30c8\u30fc\u30af\u30f3\u3092\u5229\u7528\u3002<\/li>\n<\/ul>\n<\/li>\n\n\n\n
        • \u4e00\u6642\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u6210\u3057 SSL \u8a3c\u660e\u66f8\u3092\u4fdd\u5b58<\/strong>\n
            \n
          • tempfile.NamedTemporaryFile()<\/code> \u3067\u8a3c\u660e\u66f8\u3092\u4e00\u6642\u4fdd\u5b58\u3057\u3001ssl_ca<\/code> \u306b\u8a2d\u5b9a\u3002<\/li>\n<\/ul>\n<\/li>\n\n\n\n
          • MySQL \u306b\u63a5\u7d9a<\/strong>\n
              \n
            • pymysql.connect()<\/code> \u3092\u4f7f\u3063\u3066 MySQL \u306b\u63a5\u7d9a\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
              \n\n\n\n
              \u307e\u3068\u3081<\/strong><\/h2>\n\n\n\n
              \u4eca\u56de\u306f\u3001Snowflake \u306e \u5185\u90e8\u30b9\u30c6\u30fc\u30b8<\/strong> \u306b\u4fdd\u5b58\u3055\u308c\u305f SSL\u8a3c\u660e\u66f8 (PEM\u30d5\u30a1\u30a4\u30eb)<\/strong> \u3092\u6d3b\u7528\u3057\u3001AWS RDS (MySQL) \u306b\u63a5\u7d9a\u3059\u308b\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3057\u305f\u3002<\/p>\n\n\n\n
              \u5b66\u3093\u3060\u30dd\u30a4\u30f3\u30c8<\/strong><\/h3>\n\n\n\n
              ✅ Snowflake \u306e\u30b9\u30c6\u30fc\u30b8\u304b\u3089 _snowflake.open()<\/code> \u3092\u4f7f\u3063\u3066\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3080\u65b9\u6cd5<\/strong>
              ✅ AWS RDS \u306e IAM \u8a8d\u8a3c\u3092\u4f7f\u3063\u3066\u30bb\u30ad\u30e5\u30a2\u306b MySQL \u306b\u63a5\u7d9a\u3059\u308b\u65b9\u6cd5<\/strong>
              ✅ Python UDF \u3092\u4f7f\u3063\u3066 MySQL \u306b\u63a5\u7d9a\u3059\u308b\u65b9\u6cd5<\/strong><\/p>\n\n\n\n
              \u3053\u308c\u306b\u3088\u308a\u3001Snowflake \u4e0a\u306e\u30c7\u30fc\u30bf\u3068 AWS RDS \u306e\u30c7\u30fc\u30bf\u3092\u5b89\u5168\u306b\u7d71\u5408\u3067\u304d\u308b<\/strong> \u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002
              \u540c\u3058\u3088\u3046\u306a\u8ab2\u984c\u3092\u62b1\u3048\u3066\u3044\u308b\u65b9\u306e\u53c2\u8003\u306b\u306a\u308c\u3070\u5e78\u3044\u3067\u3059\uff01💡<\/p>\n","protected":false},"excerpt":{"rendered":"
              \u3053\u3093\u306b\u3061\u306f\uff01\u4eca\u56de\u306f Snowflake <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[511],"tags":[549,137],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/posts\/3656"}],"collection":[{"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/comments?post=3656"}],"version-history":[{"count":1,"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/posts\/3656\/revisions"}],"predecessor-version":[{"id":3657,"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/posts\/3656\/revisions\/3657"}],"wp:attachment":[{"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/media?parent=3656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/categories?post=3656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blue-bear.jp\/kb\/wp-json\/wp\/v2\/tags?post=3656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}