远程数据库访问服务器

# In Google Cloud Console → Cloud Build → Triggers → Create Trigger
Name: snowflake-mcp-auto-deploy
Event: Push to branch
Branch: ^main$
Configuration: Cloud Build configuration file (cloudbuild.yaml)

# In Cloud Shell
cd ~/snowflake-mcp-remote
git pull

gcloud config set project productdatacloud
gcloud run deploy snowflake-mcp \
  --source . \
  --region=europe-west1 \
  --platform=managed \
  --allow-unauthenticated \
  --memory=2Gi \
  --cpu=2 \
  --min-instances=1 \
  --max-instances=10 \
  --concurrency=80 \
  --timeout=300s \
  --update-secrets=SNOWFLAKE_PRIVATE_KEY_CONTENT=snowflake-private-key:latest

-- UPSERT with MERGE (V2.2!)
MERGE INTO PRODUCT target
USING PRODUCT_RAW source
ON target.PRODUCT_ID = source.PRODUCT_ID
WHEN MATCHED THEN
  UPDATE SET target.JSON_DATA = source.JSON_DATA
WHEN NOT MATCHED THEN
  INSERT (PRODUCT_ID, REGION, JSON_DATA) 
  VALUES (source.PRODUCT_ID, source.REGION, source.JSON_DATA)

-- Transactions (V2.2!)
BEGIN TRANSACTION;
-- Multiple operations execute atomically
INSERT INTO TASK_QUEUE (...) VALUES (...);
UPDATE REQUEST_QUEUE SET STATUS = 'processed' WHERE ID = 123;
COMMIT;

-- Or rollback on error
BEGIN TRANSACTION;
DELETE FROM TASK_QUEUE WHERE STATUS = 'failed';
ROLLBACK;  -- Undo if needed

-- Dynamic LIMIT (V2.2!)
SELECT * FROM PRODUCT  -- Uses default 20
SELECT * FROM PRODUCT_RAW WITH max_rows=100  -- Get 100 rows
SELECT * FROM SOCIAL_MENTIONS WITH max_rows=500  -- Get 500 rows

-- Write operations
INSERT INTO TASK_QUEUE (PRODUCT_ID, STATUS) VALUES ('123', 'pending');

UPDATE TASK_QUEUE 
SET STATUS = 'completed' 
WHERE PRODUCT_ID = '123';

DELETE FROM TASK_QUEUE 
WHERE STATUS = 'completed' AND CREATED_AT < '2024-01-01';

-- DDL operations
CREATE VIEW active_products AS 
SELECT * FROM PRODUCT WHERE IS_ACTIVE = TRUE;

batch_insert(
    table="TASK_QUEUE",
    columns=["PRODUCT_ID", "STATUS", "REGION", "API_SOURCE"],
    values=[
        ["prod-001", "pending", "US", 1],
        ["prod-002", "pending", "UK", 1],
        ["prod-003", "pending", "DE", 2],
        ["prod-004", "pending", "FR", 2],
        ["prod-005", "pending", "ES", 3]
    ]
)
# Result: 5 rows inserted in 1 operation (vs 5 separate INSERTs)

-- Sync PRODUCT_RAW → PRODUCT (daily update)
MERGE INTO PRODUCT target
USING PRODUCT_RAW source
ON target.PRODUCT_ID = source.PRODUCT_ID 
   AND target.REGION = source.REGION
WHEN MATCHED THEN
  UPDATE SET 
    target.JSON_DATA = source.JSON_DATA,
    target.UPDATED_AT = CURRENT_DATE()
WHEN NOT MATCHED THEN
  INSERT (PRODUCT_ID, REQUEST_ID, REGION, JSON_DATA)
  VALUES (source.PRODUCT_ID, source.REQUEST_ID, 
          source.REGION, source.JSON_DATA);

-- Process queue items atomically
BEGIN TRANSACTION;

-- Mark items as processing
UPDATE TASK_QUEUE 
SET STATUS = 'processing', UPDATED_AT = CURRENT_DATE()
WHERE STATUS = 'pending' 
  AND CREATED_AT < DATEADD(hour, -1, CURRENT_TIMESTAMP());

-- Log processing start
INSERT INTO ETL_LOGGING (TASK_ID, STATUS, MESSAGE)
SELECT ID, 'processing', 'Batch started'
FROM TASK_QUEUE 
WHERE STATUS = 'processing';

COMMIT;

# Load 1000 products efficiently
batch_insert(
    table="PRODUCT_RAW",
    columns=["PRODUCT_ID", "REGION", "API_SOURCE", "JSON_DATA"],
    values=[[p["id"], p["region"], 1, json.dumps(p)] for p in products]
)
# 1000 rows in 1 operation vs 1000 separate INSERTs!

cd ~/snowflake-mcp-remote
python -m fastmcp dev server.py