https://pulumi.com logo
Join Slack
Powered by
I set up a Pre-Aggregation on Source Database Snow...
# general
f
I set up a Pre-Aggregation on Source Database Snowflake. However getting an error stating ‘Insufficient privileges to operate on database’
Copy code
Performing query: 0d3bb4b3-7837-4420-b91e-3334042f2ba1-span-1 
Error while querying: 0d3bb4b3-7837-4420-b91e-3334042f2ba1-span-1 (3702ms)
{
  "processingId": 1,
  "queueSize": 1,
  "queryKey": [
    [
      "CREATE TABLE arch_council_app.cube_aws_billing_cost_by_account AS SELECT\n      \"cube_aws_billing\".\"ACCOUNT_ALIAS\" \"cube_aws_billing__account_alias\", date_trunc('MONTH', CONVERT_TIMEZONE('UTC', \"cube_aws_billing\".\"BILL_DATE\"::timestamp_tz)::timestamp_ntz) \"cube_aws_billing__bill_date_month\", sum(\"cube_aws_billing\".\"SERVICE_COST\") \"cube_aws_billing__service_cost\"\n    FROM\n      \"ARCH_COUNCIL_APP\".\"AWS_BILLING\" AS \"cube_aws_billing\"  GROUP BY 1, 2",
      []
    ],
    [
      [
        {
          "refresh_key": "463123"
        }
      ]
    ]
  ],
  "queuePrefix": "SQL_PRE_AGGREGATIONS_STANDALONE_default",
  "timeInQueue": 1,
  "preAggregationId": "cube_aws_billing.cost_by_account",
  "newVersionEntry": {
    "table_name": "arch_council_app.cube_aws_billing_cost_by_account",
    "structure_version": "ulrf25hc",
    "content_version": "bxxvnrki",
    "last_updated_at": 1667246128534,
    "naming_version": 2
  },
  "preAggregation": {
    "preAggregationId": "cube_aws_billing.cost_by_account",
    "timezone": "UTC",
    "timestampFormat": "YYYY-MM-DD[T]HH:mm:ss.SSS[Z]",
    "tableName": "arch_council_app.cube_aws_billing_cost_by_account",
    "invalidateKeyQueries": [
      [
        "SELECT FLOOR((UNIX_TIMESTAMP()) / 3600) as refresh_key",
        [],
        {
          "external": true,
          "renewalThreshold": 120
        }
      ]
    ],
    "type": "rollup",
    "external": true,
    "previewSql": [
      "SELECT * FROM arch_council_app.cube_aws_billing_cost_by_account LIMIT 1000",
      []
    ],
    "preAggregationsSchema": "arch_council_app",
    "loadSql": [
      "CREATE TABLE arch_council_app.cube_aws_billing_cost_by_account AS SELECT\n      \"cube_aws_billing\".\"ACCOUNT_ALIAS\" \"cube_aws_billing__account_alias\", date_trunc('MONTH', CONVERT_TIMEZONE('UTC', \"cube_aws_billing\".\"BILL_DATE\"::timestamp_tz)::timestamp_ntz) \"cube_aws_billing__bill_date_month\", sum(\"cube_aws_billing\".\"SERVICE_COST\") \"cube_aws_billing__service_cost\"\n    FROM\n      \"ARCH_COUNCIL_APP\".\"AWS_BILLING\" AS \"cube_aws_billing\"  GROUP BY 1, 2",
      []
    ],
    "sql": [
      "SELECT\n      \"cube_aws_billing\".\"ACCOUNT_ALIAS\" \"cube_aws_billing__account_alias\", date_trunc('MONTH', CONVERT_TIMEZONE('UTC', \"cube_aws_billing\".\"BILL_DATE\"::timestamp_tz)::timestamp_ntz) \"cube_aws_billing__bill_date_month\", sum(\"cube_aws_billing\".\"SERVICE_COST\") \"cube_aws_billing__service_cost\"\n    FROM\n      \"ARCH_COUNCIL_APP\".\"AWS_BILLING\" AS \"cube_aws_billing\"  GROUP BY 1, 2",
      []
    ],
    "uniqueKeyColumns": [
      "\"cube_aws_billing__account_alias\"",
      "\"cube_aws_billing__bill_date_month\""
    ],
    "aggregationsColumns": [
      "sum(\"cube_aws_billing__service_cost\")"
    ],
    "dataSource": "default",
    "granularity": "month",
    "preAggregationStartEndQueries": [
      [
        "select min(CONVERT_TIMEZONE('UTC', \"cube_aws_billing\".\"BILL_DATE\"::timestamp_tz)::timestamp_ntz) from \"ARCH_COUNCIL_APP\".\"AWS_BILLING\" AS \"cube_aws_billing\"",
        []
      ],
      [
        "select max(CONVERT_TIMEZONE('UTC', \"cube_aws_billing\".\"BILL_DATE\"::timestamp_tz)::timestamp_ntz) from \"ARCH_COUNCIL_APP\".\"AWS_BILLING\" AS \"cube_aws_billing\"",
        []
      ]
    ],
    "indexesSql": [],
    "createTableIndexes": [],
    "readOnly": false
  },
  "addedToQueueTime": 1667246128534
} 
OperationFailedError: SQL access control error:
Insufficient privileges to operate on database 'GBI_OTHERS_DATA_ENG_DB'
    at createError (/cube/node_modules/snowflake-sdk/lib/errors.js:536:15)
    at Object.exports.createOperationFailedError (/cube/node_modules/snowflake-sdk/lib/errors.js:315:10)
    at Object.callback (/cube/node_modules/snowflake-sdk/lib/services/sf.js:647:28)
    at /cube/node_modules/snowflake-sdk/lib/http/base.js:111:25
    at done (/cube/node_modules/urllib/lib/urllib.js:589:5)
    at /cube/node_modules/urllib/lib/urllib.js:953:9
    at decodeContent (/cube/node_modules/urllib/lib/urllib.js:740:14)
    at handleResponseCloseAndEnd (/cube/node_modules/urllib/lib/urllib.js:924:7)
    at IncomingMessage.<anonymous> (/cube/node_modules/urllib/lib/urllib.js:962:7)
    at IncomingMessage.emit (events.js:412:35)
    at IncomingMessage.emit (domain.js:475:12)
    at endReadableNT (internal/streams/readable.js:1333:12)
    at processTicksAndRejections (internal/process/task_queues.js:82:21)
Error querying db: 0d3bb4b3-7837-4420-b91e-3334042f2ba1-span-1 
--
"SELECT `cube_aws_billing__account_alias` `cube_aws_billing__account_alias`, sum(`cube_aws_billing__service_cost`) `cube_aws_billing__service_cost` FROM arch_council_app.cube_aws_billing_cost_by_account AS `cube_aws_billing__cost_by_account` GROUP BY 1 ORDER BY 2 DESC LIMIT 50"
--
{
  "params": []
} 
Error: SQL access control error:
Insufficient privileges to operate on database 'GBI_OTHERS_DATA_ENG_DB'
    at QueryQueue.parseResult (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:146:13)
    at QueryQueue.executeInQueue (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:135:19)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at PreAggregationLoader.loadPreAggregationWithKeys (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:742:7)
    at preAggregationPromise (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:1946:28)
    at QueryOrchestrator.fetchQuery (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryOrchestrator.ts:158:59)
    at OrchestratorApi.executeQuery (/cube/node_modules/@cubejs-backend/server-core/src/core/OrchestratorApi.ts:85:20)
    at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1230:21
    at async Promise.all (index 0)
    at ApiGateway.getSqlResponseInternal (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1228:31)
    at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1357:28
    at async Promise.all (index 0)
    at ApiGateway.load (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1348:23)
    at /cube/node_modules/@cubejs-backend/api-gateway/src/sql-server.ts:101:13
Orchestrator error: 0d3bb4b3-7837-4420-b91e-3334042f2ba1-span-1 (3832ms)
--
{
  "measures": [
    "cube_aws_billing.service_cost"
  ],
  "dimensions": [
    "cube_aws_billing.account_alias"
  ],
  "segments": [],
  "order": [
    [
      "cube_aws_billing.service_cost",
      "desc"
    ]
  ],
  "limit": 50
}
--
{
  "securityContext": {},
  "appName": "NULL",
  "protocol": "postgres",
  "apiType": "sql"
} 
Error: SQL access control error:
Insufficient privileges to operate on database 'GBI_OTHERS_DATA_ENG_DB'
2022-10-31 19:55:32,267 ERROR [cubejs_native::transport] [transport] load - strange response, success which contains error: V1Error { error: "Error: SQL access control error:\nInsufficient privileges to operate on database 'GBI_OTHERS_DATA_ENG_DB'" }
Cube SQL Error: undefined 
{
  "apiType": "sql",
  "protocol": "postgres",
  "appName": "NULL"
} 
Error during processing PostgreSQL message: Internal: Execution error: Internal: Error: SQL access control error:
Insufficient privileges to operate on database 'GBI_OTHERS_DATA_ENG_DB'
However I am able to execute the create table as query with the
Copy code
CUBEJS_DB_USER=gbi_others_data_eng_db_arch_council_user
Copy code
CREATE TABLE arch_council_app.cube_aws_billing_cost_by_account AS SELECT 
cube_aws_billing.ACCOUNT_ALIAS cube_aws_billing__account_alias, 
date_trunc('MONTH', CONVERT_TIMEZONE('UTC', cube_aws_billing.BILL_DATE::timestamp_tz)::timestamp_ntz) cube_aws_billing__bill_date_month, 
sum(cube_aws_billing.SERVICE_COST) cube_aws_billing__service_cost   
FROM ARCH_COUNCIL_APP.AWS_BILLING AS cube_aws_billing  
GROUP BY 1, 2
What am I missing here?
s
you are prob executing them with different roles…
the role through pulumi is the same role that you are executing through the Console?
I find it less confusing when I pass a specific Provider when I run a query or create a resource… https://www.pulumi.com/registry/packages/snowflake/api-docs/provider/
f
I am setting the env variable in the .env file
Copy code
CUBEJS_DB_SNOWFLAKE_ROLE=
Sorry, I am new to cube and don’t understand how Pulumi fits into the picture. I am providing all configs through the .env file
Open in Slack
PreviousNext
Powered by