Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Priviliges Error on Table Input component

We have a simple transformation job which is reading data from a Snowflake table, via a Table Input component. This job isn't new, and no changes have been made since it was created. But all of a sudden, the job is failing to validate. The Table Input component is reporting the following:

SQL compilation error:
View 'v_0000036566_0000342233' already exists, but current role has no privileges on it. If this is unexpected and you cannot resolve this problem, contact your system administrator. ACCOUNTADMIN role may be required to manage the privileges on the object.

Googling that message, it appears to be an error from Snowflake, not an error from Matillion. But it doesn't make sense. The job is not attempting to create any objects, not using views, and we don't know what that view is. Has anyone run into this before?

4 Community Answers

Matillion Agent  

Damian Chan —

Hello Dillon,

Would you be able to provide us your server logs, please? This can be found from the Admin Menu → Download Server Log.

Thank you.

Best Regards,
Damian


Dillon Pritchard —

Sure thing, could you let me know the best way to get it to you? The log is quite large. Looking at it though, I now understand that views starting in v_ are temporary objects used by Matillion, and it is indeed trying to drop and recreate them behind the scenes. Here's an excerpt of where it fails:

12-Dec-2018 17:24:59.160 INFO [task-thread-45] com.matillion.bi.emerald.server.model.databasecontroller.QueryLogger.info DROP VIEW IF EXISTS "v_0000036566_0000342277" CASCADE;
12-Dec-2018 17:24:59.292 INFO [task-thread-45] com.matillion.bi.emerald.server.model.databasecontroller.QueryLogger.info CREATE VIEW "v_0000036566_0000342277" AS (SELECT "ALARMID", "ALERTKEY"...
12-Dec-2018 17:24:59.785 WARNING [task-thread-45] com.matillion.bi.emerald.server.model.databasecontroller.QueryLogger.error Error executing query:
CREATE VIEW "v_0000036566_0000342277" AS (SELECT
"ALARMID",
"ALERTKEY",
"NODEHASHKEY",
"ACKNOWLEDGED",
"ACKNOWLEDGEID",
"ACKNOWLEDGETIME",
"AGENT",
"ALERTGROUP",
"FISRTOCCUERRENCE",
"LASTOCCURRENCE",
"NODENAME",
"READTIME",
"SEVERITY",
"SERVERSERIAL"
FROM "ROC_COOKED"."LIGHTHOUSE"."ALARM_PROCESSING")
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
View 'v_0000036566_0000342277' already exists, but current role has no privileges on it. If this is unexpected and you cannot resolve this problem, contact your system administrator. ACCOUNTADMIN role may be required to manage the privileges on the object.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:88)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:384)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:420)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:239)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:179)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:152)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:635)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:186)
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.execute(SnowflakePreparedStatementV1.java:420)
at com.matillion.bi.emerald.server.jdbc.controller.JDBCDatabaseController.executeQuery(JDBCDatabaseController.java:237)
at com.matillion.bi.emerald.server.jdbc.controller.JDBCDatabaseController.executeQuery(JDBCDatabaseController.java:201)
at com.matillion.bi.emerald.server.jdbc.controller.JDBCContextDatabaseController.executeQuery(JDBCContextDatabaseController.java:136)
at com.matillion.bi.emerald.server.jdbc.controller.JDBCDatabaseController.executeQuery(JDBCDatabaseController.java:332)
at com.matillion.bi.emerald.server.snowflake.controller.SnowflakeJDBCControllerHelper.recreateView(SnowflakeJDBCControllerHelper.java:348)
at com.matillion.bi.emerald.server.jdbc.controller.JDBCContextDatabaseController.recreateView(JDBCContextDatabaseController.java:373)
at com.matillion.bi.emerald.shared.component.environment.EnvironmentSF.recreateView(EnvironmentSF.java:221)
at com.matillion.bi.emerald.shared.component.environment.EnvironmentSF.addMetadata(EnvironmentSF.java:315)
at com.matillion.bi.emerald.shared.component.implementation.TransformationComponentImplementation.populateMetadata(TransformationComponentImplementation.java:196)
at com.matillion.bi.emerald.shared.component.implementation.TransformationComponentImplementation.addComponentMetadata(TransformationComponentImplementation.java:171)
at com.matillion.bi.emerald.server.controller.ValidationService.createInDatabase(ValidationService.java:250)
at com.matillion.bi.emerald.server.controller.ValidationService.createView(ValidationService.java:221)
at com.matillion.bi.emerald.server.taskrunner.transformation.tasks.CreateView.runContextTask(CreateView.java:52)
at com.matillion.bi.emerald.server.taskrunner.ContextTask.runTask(ContextTask.java:82)
at com.matillion.bi.emerald.server.taskrunner.EmeraldTask.run(EmeraldTask.java:214)
at com.matillion.bi.emerald.server.taskrunner.TaskThread.run(TaskThread.java:29)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)


Matillion Agent  

Damian Chan —

Hello Dillon,

So as far you know you the credentials you are using have full access to Snowflake?

Let’s rotate your log file so that it’s smaller. Would you be able to SSH into your instance and then run the following command.

sudo logrotate —force /etc/logrotate.d/tomcat8

From there you’ll want to run your job so that you generate the error again. After that can you download the server log again and send to us, please? Thank you.

Best Regards,
Damian


Dillon Pritchard —

Yes, the credentials do have full access. We were able to resolve the issue by rebuilding the job.

Post Your Community Answer

To add an answer please login