0

Can we get bin log of DML statements execution in Google Cloud Spanner. Tried enabling "Data Access Write" Logs, but it does not give the same bin log as mysql or other relational databases.

https://cloud.google.com/spanner/docs/audit-logging

https://cloud.google.com/logging/docs/audit/configure-data-access

https://console.cloud.google.com/iam-admin/audit?_ga=2.231523607.908127545.1587027457-1147786289.1577266273

Chandan Bhattad
  • 351
  • 1
  • 5
  • 21
  • Data access write logs should now have dml statements in the protoPayload.request.sql field for ExecuteSql and ExecuteStreamingSql calls. Could you check and see if your queries are there? For the streaming queries, you'll see two log entries per query, one marking start and another marking end -- they will have matching operation.id fields, the start entry will have operation.first set to true; the request field is only in the start log entry. – Ozan Bellik Apr 23 '20 at 18:42

2 Answers2

0

Cloud Spanner doesnt include a build in utility to generate binlogs like MySQL. You will have to implement your own change log table to keep track of the changes.

You can also make use of the Audit logs to keep track of Who is performing the changes. So using both the Audit logs and your own change log table would be the ideal solution to track your changes.

Chris32
  • 4,716
  • 2
  • 18
  • 30
  • ExecuteSql and ExecuteStreamingSql calls do log dml statements now in data access write audit logs. – Ozan Bellik Apr 23 '20 at 18:44
  • Those logs are of very little use. They just log the sql executed. Example: "update table set v = 1 where condition" . Also if your query consists of a paramater, it doesn't log the value of the parameter. Example: "update table set v = 1 where col1 in :col1list " where col1list is a parameter. – Chandan Bhattad Apr 24 '20 at 11:33
  • Thanks for the feedback, Chandan. Besides the missing parameter value, could you tell us what additional data you're looking for that you would find in binlogs? As I understand, binlogs are primarily used for replication and DR, which Cloud Spanner takes care of for you. Would love to have some info on other use cases. Thanks. – Ozan Bellik Apr 24 '20 at 17:30
  • The best thing would be to have same bin log as mysql, don't know why it is not the case in the first place. Adding the parameter would be helpful, but then we still would have to reconstruct the query. If the actual query that got executed is logged, would be much convenient – Chandan Bhattad Apr 26 '20 at 19:14
  • Say, we are building some other dataset in datawarehouse (bq or redshift) which relies changes in spanner. It is very easy if we have bin log like mysql. For eg: For update bin log, it clearly says what were the old values and what are the updated column values for a row @OzanBellik – Chandan Bhattad Apr 26 '20 at 19:17
  • Ah, sounds like you're looking for Change Data Capture? We can take that as a feature request. Thank you for the input. For context, Spanner has major implementation differences from a traditional db like mysql. For mysql, bin logs are a means to enable replication. For Spanner, which was designed from the start for high availability at high scale, replication is built in. – Ozan Bellik Apr 27 '20 at 23:38
0

Data access audit logs for ExecuteSql and ExecuteStreamingSql contain the SQL statement, so you can produce a log of DML statements by enabling “Data Write” audit logs for “Cloud Spanner API”, as described in https://cloud.google.com/logging/docs/audit/configure-data-access#config-console-enable:

enabling data write audit logs

The SQL statement is contained in the single log entry generated for ExecuteSql or the first log entry generated for ExecuteStreamingSql, in the field protoPayload.request.sql. For example, here are the log entries generated for an ExecuteStreamingSql request:

example audit logs for an ExecuteStreamingSql request

Kristen Kozak
  • 9
  • 2