We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Switch to Standard SQL for BigQuery as the default
I think it's time to switch to use Standard SQL as the default syntax for BigQuery.

Also, we should show table names in the schema browser in the Standard SQL format instead of the legacy one (`.` as separator instead of `:`).

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `redash/query_runner/big_query.py`
Content:
```
1 import datetime
2 import logging
3 import sys
4 import time
5 from base64 import b64decode
6 
7 import httplib2
8 import requests
9 
10 from redash import settings
11 from redash.query_runner import *
12 from redash.utils import json_dumps, json_loads
13 
14 logger = logging.getLogger(__name__)
15 
16 try:
17     import apiclient.errors
18     from apiclient.discovery import build
19     from apiclient.errors import HttpError
20     from oauth2client.service_account import ServiceAccountCredentials
21     from oauth2client.contrib import gce
22 
23     enabled = True
24 except ImportError:
25     enabled = False
26 
27 types_map = {
28     'INTEGER': TYPE_INTEGER,
29     'FLOAT': TYPE_FLOAT,
30     'BOOLEAN': TYPE_BOOLEAN,
31     'STRING': TYPE_STRING,
32     'TIMESTAMP': TYPE_DATETIME,
33 }
34 
35 
36 def transform_row(row, fields):
37     column_index = 0
38     row_data = {}
39 
40     for cell in row["f"]:
41         field = fields[column_index]
42         cell_value = cell['v']
43 
44         if cell_value is None:
45             pass
46         # Otherwise just cast the value
47         elif field['type'] == 'INTEGER':
48             cell_value = int(cell_value)
49         elif field['type'] == 'FLOAT':
50             cell_value = float(cell_value)
51         elif field['type'] == 'BOOLEAN':
52             cell_value = cell_value.lower() == "true"
53         elif field['type'] == 'TIMESTAMP':
54             cell_value = datetime.datetime.fromtimestamp(float(cell_value))
55 
56         row_data[field["name"]] = cell_value
57         column_index += 1
58 
59     return row_data
60 
61 
62 def _load_key(filename):
63     f = file(filename, "rb")
64     try:
65         return f.read()
66     finally:
67         f.close()
68 
69 
70 def _get_query_results(jobs, project_id, location, job_id, start_index):
71     query_reply = jobs.getQueryResults(projectId=project_id,
72                                        location=location,
73                                        jobId=job_id,
74                                        startIndex=start_index).execute()
75     logging.debug('query_reply %s', query_reply)
76     if not query_reply['jobComplete']:
77         time.sleep(10)
78         return _get_query_results(jobs, project_id, location, job_id, start_index)
79 
80     return query_reply
81 
82 
83 class BigQuery(BaseQueryRunner):
84     noop_query = "SELECT 1"
85 
86     @classmethod
87     def enabled(cls):
88         return enabled
89 
90     @classmethod
91     def configuration_schema(cls):
92         return {
93             'type': 'object',
94             'properties': {
95                 'projectId': {
96                     'type': 'string',
97                     'title': 'Project ID'
98                 },
99                 'jsonKeyFile': {
100                     "type": "string",
101                     'title': 'JSON Key File'
102                 },
103                 'totalMBytesProcessedLimit': {
104                     "type": "number",
105                     'title': 'Scanned Data Limit (MB)'
106                 },
107                 'userDefinedFunctionResourceUri': {
108                     "type": "string",
109                     'title': 'UDF Source URIs (i.e. gs://bucket/date_utils.js, gs://bucket/string_utils.js )'
110                 },
111                 'useStandardSql': {
112                     "type": "boolean",
113                     'title': "Use Standard SQL (Beta)",
114                 },
115                 'location': {
116                     "type": "string",
117                     "title": "Processing Location",
118                 },
119                 'loadSchema': {
120                     "type": "boolean",
121                     "title": "Load Schema"
122                 },
123                 'maximumBillingTier': {
124                     "type": "number",
125                     "title": "Maximum Billing Tier"
126                 }
127             },
128             'required': ['jsonKeyFile', 'projectId'],
129             "order": ['projectId', 'jsonKeyFile', 'loadSchema', 'useStandardSql', 'location', 'totalMBytesProcessedLimit', 'maximumBillingTier', 'userDefinedFunctionResourceUri'],
130             'secret': ['jsonKeyFile']
131         }
132 
133     @classmethod
134     def annotate_query(cls):
135         return False
136 
137     def _get_bigquery_service(self):
138         scope = [
139             "https://www.googleapis.com/auth/bigquery",
140             "https://www.googleapis.com/auth/drive"
141         ]
142 
143         key = json_loads(b64decode(self.configuration['jsonKeyFile']))
144 
145         creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)
146         http = httplib2.Http(timeout=settings.BIGQUERY_HTTP_TIMEOUT)
147         http = creds.authorize(http)
148 
149         return build("bigquery", "v2", http=http)
150 
151     def _get_project_id(self):
152         return self.configuration["projectId"]
153 
154     def _get_location(self):
155         return self.configuration.get("location")
156 
157     def _get_total_bytes_processed(self, jobs, query):
158         job_data = {
159             "query": query,
160             "dryRun": True,
161         }
162 
163         if self._get_location():
164             job_data['location'] = self._get_location()
165 
166         if self.configuration.get('useStandardSql', False):
167             job_data['useLegacySql'] = False
168 
169         response = jobs.query(projectId=self._get_project_id(), body=job_data).execute()
170         return int(response["totalBytesProcessed"])
171 
172     def _get_query_result(self, jobs, query):
173         project_id = self._get_project_id()
174         job_data = {
175             "configuration": {
176                 "query": {
177                     "query": query,
178                 }
179             }
180         }
181 
182         if self._get_location():
183             job_data['jobReference'] = {
184                 'location': self._get_location()
185             }
186 
187         if self.configuration.get('useStandardSql', False):
188             job_data['configuration']['query']['useLegacySql'] = False
189 
190         if self.configuration.get('userDefinedFunctionResourceUri'):
191             resource_uris = self.configuration["userDefinedFunctionResourceUri"].split(',')
192             job_data["configuration"]["query"]["userDefinedFunctionResources"] = map(
193                 lambda resource_uri: {"resourceUri": resource_uri}, resource_uris)
194 
195         if "maximumBillingTier" in self.configuration:
196             job_data["configuration"]["query"]["maximumBillingTier"] = self.configuration["maximumBillingTier"]
197 
198         insert_response = jobs.insert(projectId=project_id, body=job_data).execute()
199         current_row = 0
200         query_reply = _get_query_results(jobs, project_id=project_id, location=self._get_location(),
201                                          job_id=insert_response['jobReference']['jobId'], start_index=current_row)
202 
203         logger.debug("bigquery replied: %s", query_reply)
204 
205         rows = []
206 
207         while ("rows" in query_reply) and current_row < query_reply['totalRows']:
208             for row in query_reply["rows"]:
209                 rows.append(transform_row(row, query_reply["schema"]["fields"]))
210 
211             current_row += len(query_reply['rows'])
212 
213             query_result_request = {
214                 'projectId': project_id,
215                 'jobId': query_reply['jobReference']['jobId'],
216                 'startIndex': current_row
217             }
218 
219             if self._get_location():
220                 query_result_request['location'] = self._get_location()
221 
222             query_reply = jobs.getQueryResults(**query_result_request).execute()
223 
224         columns = [{'name': f["name"],
225                     'friendly_name': f["name"],
226                     'type': types_map.get(f['type'], "string")} for f in query_reply["schema"]["fields"]]
227 
228         data = {
229             "columns": columns,
230             "rows": rows,
231             'metadata': {'data_scanned': int(query_reply['totalBytesProcessed'])}
232         }
233 
234         return data
235 
236     def _get_columns_schema(self, table_data):
237         columns = []
238         for column in table_data['schema']['fields']:
239             columns.extend(self._get_columns_schema_column(column))
240 
241         return {'name': table_data['id'], 'columns': columns}
242 
243     def _get_columns_schema_column(self, column):
244         columns = []
245         if column['type'] == 'RECORD':
246             for field in column['fields']:
247                 columns.append(u"{}.{}".format(column['name'], field['name']))
248         else:
249             columns.append(column['name'])
250 
251         return columns
252 
253     def get_schema(self, get_stats=False):
254         if not self.configuration.get('loadSchema', False):
255             return []
256 
257         service = self._get_bigquery_service()
258         project_id = self._get_project_id()
259         datasets = service.datasets().list(projectId=project_id).execute()
260         schema = []
261         for dataset in datasets.get('datasets', []):
262             dataset_id = dataset['datasetReference']['datasetId']
263             tables = service.tables().list(projectId=project_id, datasetId=dataset_id).execute()
264             while True:
265                 for table in tables.get('tables', []):
266                     table_data = service.tables().get(projectId=project_id,
267                                                       datasetId=dataset_id,
268                                                       tableId=table['tableReference']['tableId']).execute()
269                     table_schema = self._get_columns_schema(table_data)
270                     schema.append(table_schema)
271 
272                 next_token = tables.get('nextPageToken', None)
273                 if next_token is None:
274                     break
275 
276                 tables = service.tables().list(projectId=project_id,
277                                                datasetId=dataset_id,
278                                                pageToken=next_token).execute()
279 
280         return schema
281 
282     def run_query(self, query, user):
283         logger.debug("BigQuery got query: %s", query)
284 
285         bigquery_service = self._get_bigquery_service()
286         jobs = bigquery_service.jobs()
287 
288         try:
289             if "totalMBytesProcessedLimit" in self.configuration:
290                 limitMB = self.configuration["totalMBytesProcessedLimit"]
291                 processedMB = self._get_total_bytes_processed(jobs, query) / 1000.0 / 1000.0
292                 if limitMB < processedMB:
293                     return None, "Larger than %d MBytes will be processed (%f MBytes)" % (limitMB, processedMB)
294 
295             data = self._get_query_result(jobs, query)
296             error = None
297 
298             json_data = json_dumps(data)
299         except apiclient.errors.HttpError as e:
300             json_data = None
301             if e.resp.status == 400:
302                 error = json_loads(e.content)['error']['message']
303             else:
304                 error = e.content
305         except KeyboardInterrupt:
306             error = "Query cancelled by user."
307             json_data = None
308 
309         return json_data, error
310 
311 
312 class BigQueryGCE(BigQuery):
313     @classmethod
314     def type(cls):
315         return "bigquery_gce"
316 
317     @classmethod
318     def enabled(cls):
319         try:
320             # check if we're on a GCE instance
321             requests.get('http://metadata.google.internal')
322         except requests.exceptions.ConnectionError:
323             return False
324 
325         return True
326 
327     @classmethod
328     def configuration_schema(cls):
329         return {
330             'type': 'object',
331             'properties': {
332                 'totalMBytesProcessedLimit': {
333                     "type": "number",
334                     'title': 'Total MByte Processed Limit'
335                 },
336                 'userDefinedFunctionResourceUri': {
337                     "type": "string",
338                     'title': 'UDF Source URIs (i.e. gs://bucket/date_utils.js, gs://bucket/string_utils.js )'
339                 },
340                 'useStandardSql': {
341                     "type": "boolean",
342                     'title': "Use Standard SQL (Beta)",
343                 },
344                 'location': {
345                     "type": "string",
346                     "title": "Processing Location",
347                     "default": "US",
348                 },
349                 'loadSchema': {
350                     "type": "boolean",
351                     "title": "Load Schema"
352                 }
353             }
354         }
355 
356     def _get_project_id(self):
357         return requests.get('http://metadata/computeMetadata/v1/project/project-id', headers={'Metadata-Flavor': 'Google'}).content
358 
359     def _get_bigquery_service(self):
360         credentials = gce.AppAssertionCredentials(scope='https://www.googleapis.com/auth/bigquery')
361         http = httplib2.Http()
362         http = credentials.authorize(http)
363 
364         return build("bigquery", "v2", http=http)
365 
366 
367 register(BigQuery)
368 register(BigQueryGCE)
369
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/redash/query_runner/big_query.py b/redash/query_runner/big_query.py
--- a/redash/query_runner/big_query.py
+++ b/redash/query_runner/big_query.py
@@ -110,7 +110,8 @@
                 },
                 'useStandardSql': {
                     "type": "boolean",
-                    'title': "Use Standard SQL (Beta)",
+                    'title': "Use Standard SQL",
+                    "default": True,
                 },
                 'location': {
                     "type": "string",
@@ -238,7 +239,9 @@
         for column in table_data['schema']['fields']:
             columns.extend(self._get_columns_schema_column(column))
 
-        return {'name': table_data['id'], 'columns': columns}
+        project_id = self._get_project_id()
+        table_name = table_data['id'].replace("%s:" % project_id, "")
+        return {'name': table_name, 'columns': columns}
 
     def _get_columns_schema_column(self, column):
         columns = []
@@ -339,7 +342,8 @@
                 },
                 'useStandardSql': {
                     "type": "boolean",
-                    'title': "Use Standard SQL (Beta)",
+                    'title': "Use Standard SQL",
+                    "default": True,
                 },
                 'location': {
                     "type": "string",

{"golden_diff": "diff --git a/redash/query_runner/big_query.py b/redash/query_runner/big_query.py\n--- a/redash/query_runner/big_query.py\n+++ b/redash/query_runner/big_query.py\n@@ -110,7 +110,8 @@\n                 },\n                 'useStandardSql': {\n                     \"type\": \"boolean\",\n-                    'title': \"Use Standard SQL (Beta)\",\n+                    'title': \"Use Standard SQL\",\n+                    \"default\": True,\n                 },\n                 'location': {\n                     \"type\": \"string\",\n@@ -238,7 +239,9 @@\n         for column in table_data['schema']['fields']:\n             columns.extend(self._get_columns_schema_column(column))\n \n-        return {'name': table_data['id'], 'columns': columns}\n+        project_id = self._get_project_id()\n+        table_name = table_data['id'].replace(\"%s:\" % project_id, \"\")\n+        return {'name': table_name, 'columns': columns}\n \n     def _get_columns_schema_column(self, column):\n         columns = []\n@@ -339,7 +342,8 @@\n                 },\n                 'useStandardSql': {\n                     \"type\": \"boolean\",\n-                    'title': \"Use Standard SQL (Beta)\",\n+                    'title': \"Use Standard SQL\",\n+                    \"default\": True,\n                 },\n                 'location': {\n                     \"type\": \"string\",\n", "issue": "Switch to Standard SQL for BigQuery as the default\nI think it's time to switch to use Standard SQL as the default syntax for BigQuery.\r\n\r\nAlso, we should show table names in the schema browser in the Standard SQL format instead of the legacy one (`.` as separator instead of `:`).\n", "before_files": [{"content": "import datetime\nimport logging\nimport sys\nimport time\nfrom base64 import b64decode\n\nimport httplib2\nimport requests\n\nfrom redash import settings\nfrom redash.query_runner import *\nfrom redash.utils import json_dumps, json_loads\n\nlogger = logging.getLogger(__name__)\n\ntry:\n    import apiclient.errors\n    from apiclient.discovery import build\n    from apiclient.errors import HttpError\n    from oauth2client.service_account import ServiceAccountCredentials\n    from oauth2client.contrib import gce\n\n    enabled = True\nexcept ImportError:\n    enabled = False\n\ntypes_map = {\n    'INTEGER': TYPE_INTEGER,\n    'FLOAT': TYPE_FLOAT,\n    'BOOLEAN': TYPE_BOOLEAN,\n    'STRING': TYPE_STRING,\n    'TIMESTAMP': TYPE_DATETIME,\n}\n\n\ndef transform_row(row, fields):\n    column_index = 0\n    row_data = {}\n\n    for cell in row[\"f\"]:\n        field = fields[column_index]\n        cell_value = cell['v']\n\n        if cell_value is None:\n            pass\n        # Otherwise just cast the value\n        elif field['type'] == 'INTEGER':\n            cell_value = int(cell_value)\n        elif field['type'] == 'FLOAT':\n            cell_value = float(cell_value)\n        elif field['type'] == 'BOOLEAN':\n            cell_value = cell_value.lower() == \"true\"\n        elif field['type'] == 'TIMESTAMP':\n            cell_value = datetime.datetime.fromtimestamp(float(cell_value))\n\n        row_data[field[\"name\"]] = cell_value\n        column_index += 1\n\n    return row_data\n\n\ndef _load_key(filename):\n    f = file(filename, \"rb\")\n    try:\n        return f.read()\n    finally:\n        f.close()\n\n\ndef _get_query_results(jobs, project_id, location, job_id, start_index):\n    query_reply = jobs.getQueryResults(projectId=project_id,\n                                       location=location,\n                                       jobId=job_id,\n                                       startIndex=start_index).execute()\n    logging.debug('query_reply %s', query_reply)\n    if not query_reply['jobComplete']:\n        time.sleep(10)\n        return _get_query_results(jobs, project_id, location, job_id, start_index)\n\n    return query_reply\n\n\nclass BigQuery(BaseQueryRunner):\n    noop_query = \"SELECT 1\"\n\n    @classmethod\n    def enabled(cls):\n        return enabled\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            'type': 'object',\n            'properties': {\n                'projectId': {\n                    'type': 'string',\n                    'title': 'Project ID'\n                },\n                'jsonKeyFile': {\n                    \"type\": \"string\",\n                    'title': 'JSON Key File'\n                },\n                'totalMBytesProcessedLimit': {\n                    \"type\": \"number\",\n                    'title': 'Scanned Data Limit (MB)'\n                },\n                'userDefinedFunctionResourceUri': {\n                    \"type\": \"string\",\n                    'title': 'UDF Source URIs (i.e. gs://bucket/date_utils.js, gs://bucket/string_utils.js )'\n                },\n                'useStandardSql': {\n                    \"type\": \"boolean\",\n                    'title': \"Use Standard SQL (Beta)\",\n                },\n                'location': {\n                    \"type\": \"string\",\n                    \"title\": \"Processing Location\",\n                },\n                'loadSchema': {\n                    \"type\": \"boolean\",\n                    \"title\": \"Load Schema\"\n                },\n                'maximumBillingTier': {\n                    \"type\": \"number\",\n                    \"title\": \"Maximum Billing Tier\"\n                }\n            },\n            'required': ['jsonKeyFile', 'projectId'],\n            \"order\": ['projectId', 'jsonKeyFile', 'loadSchema', 'useStandardSql', 'location', 'totalMBytesProcessedLimit', 'maximumBillingTier', 'userDefinedFunctionResourceUri'],\n            'secret': ['jsonKeyFile']\n        }\n\n    @classmethod\n    def annotate_query(cls):\n        return False\n\n    def _get_bigquery_service(self):\n        scope = [\n            \"https://www.googleapis.com/auth/bigquery\",\n            \"https://www.googleapis.com/auth/drive\"\n        ]\n\n        key = json_loads(b64decode(self.configuration['jsonKeyFile']))\n\n        creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)\n        http = httplib2.Http(timeout=settings.BIGQUERY_HTTP_TIMEOUT)\n        http = creds.authorize(http)\n\n        return build(\"bigquery\", \"v2\", http=http)\n\n    def _get_project_id(self):\n        return self.configuration[\"projectId\"]\n\n    def _get_location(self):\n        return self.configuration.get(\"location\")\n\n    def _get_total_bytes_processed(self, jobs, query):\n        job_data = {\n            \"query\": query,\n            \"dryRun\": True,\n        }\n\n        if self._get_location():\n            job_data['location'] = self._get_location()\n\n        if self.configuration.get('useStandardSql', False):\n            job_data['useLegacySql'] = False\n\n        response = jobs.query(projectId=self._get_project_id(), body=job_data).execute()\n        return int(response[\"totalBytesProcessed\"])\n\n    def _get_query_result(self, jobs, query):\n        project_id = self._get_project_id()\n        job_data = {\n            \"configuration\": {\n                \"query\": {\n                    \"query\": query,\n                }\n            }\n        }\n\n        if self._get_location():\n            job_data['jobReference'] = {\n                'location': self._get_location()\n            }\n\n        if self.configuration.get('useStandardSql', False):\n            job_data['configuration']['query']['useLegacySql'] = False\n\n        if self.configuration.get('userDefinedFunctionResourceUri'):\n            resource_uris = self.configuration[\"userDefinedFunctionResourceUri\"].split(',')\n            job_data[\"configuration\"][\"query\"][\"userDefinedFunctionResources\"] = map(\n                lambda resource_uri: {\"resourceUri\": resource_uri}, resource_uris)\n\n        if \"maximumBillingTier\" in self.configuration:\n            job_data[\"configuration\"][\"query\"][\"maximumBillingTier\"] = self.configuration[\"maximumBillingTier\"]\n\n        insert_response = jobs.insert(projectId=project_id, body=job_data).execute()\n        current_row = 0\n        query_reply = _get_query_results(jobs, project_id=project_id, location=self._get_location(),\n                                         job_id=insert_response['jobReference']['jobId'], start_index=current_row)\n\n        logger.debug(\"bigquery replied: %s\", query_reply)\n\n        rows = []\n\n        while (\"rows\" in query_reply) and current_row < query_reply['totalRows']:\n            for row in query_reply[\"rows\"]:\n                rows.append(transform_row(row, query_reply[\"schema\"][\"fields\"]))\n\n            current_row += len(query_reply['rows'])\n\n            query_result_request = {\n                'projectId': project_id,\n                'jobId': query_reply['jobReference']['jobId'],\n                'startIndex': current_row\n            }\n\n            if self._get_location():\n                query_result_request['location'] = self._get_location()\n\n            query_reply = jobs.getQueryResults(**query_result_request).execute()\n\n        columns = [{'name': f[\"name\"],\n                    'friendly_name': f[\"name\"],\n                    'type': types_map.get(f['type'], \"string\")} for f in query_reply[\"schema\"][\"fields\"]]\n\n        data = {\n            \"columns\": columns,\n            \"rows\": rows,\n            'metadata': {'data_scanned': int(query_reply['totalBytesProcessed'])}\n        }\n\n        return data\n\n    def _get_columns_schema(self, table_data):\n        columns = []\n        for column in table_data['schema']['fields']:\n            columns.extend(self._get_columns_schema_column(column))\n\n        return {'name': table_data['id'], 'columns': columns}\n\n    def _get_columns_schema_column(self, column):\n        columns = []\n        if column['type'] == 'RECORD':\n            for field in column['fields']:\n                columns.append(u\"{}.{}\".format(column['name'], field['name']))\n        else:\n            columns.append(column['name'])\n\n        return columns\n\n    def get_schema(self, get_stats=False):\n        if not self.configuration.get('loadSchema', False):\n            return []\n\n        service = self._get_bigquery_service()\n        project_id = self._get_project_id()\n        datasets = service.datasets().list(projectId=project_id).execute()\n        schema = []\n        for dataset in datasets.get('datasets', []):\n            dataset_id = dataset['datasetReference']['datasetId']\n            tables = service.tables().list(projectId=project_id, datasetId=dataset_id).execute()\n            while True:\n                for table in tables.get('tables', []):\n                    table_data = service.tables().get(projectId=project_id,\n                                                      datasetId=dataset_id,\n                                                      tableId=table['tableReference']['tableId']).execute()\n                    table_schema = self._get_columns_schema(table_data)\n                    schema.append(table_schema)\n\n                next_token = tables.get('nextPageToken', None)\n                if next_token is None:\n                    break\n\n                tables = service.tables().list(projectId=project_id,\n                                               datasetId=dataset_id,\n                                               pageToken=next_token).execute()\n\n        return schema\n\n    def run_query(self, query, user):\n        logger.debug(\"BigQuery got query: %s\", query)\n\n        bigquery_service = self._get_bigquery_service()\n        jobs = bigquery_service.jobs()\n\n        try:\n            if \"totalMBytesProcessedLimit\" in self.configuration:\n                limitMB = self.configuration[\"totalMBytesProcessedLimit\"]\n                processedMB = self._get_total_bytes_processed(jobs, query) / 1000.0 / 1000.0\n                if limitMB < processedMB:\n                    return None, \"Larger than %d MBytes will be processed (%f MBytes)\" % (limitMB, processedMB)\n\n            data = self._get_query_result(jobs, query)\n            error = None\n\n            json_data = json_dumps(data)\n        except apiclient.errors.HttpError as e:\n            json_data = None\n            if e.resp.status == 400:\n                error = json_loads(e.content)['error']['message']\n            else:\n                error = e.content\n        except KeyboardInterrupt:\n            error = \"Query cancelled by user.\"\n            json_data = None\n\n        return json_data, error\n\n\nclass BigQueryGCE(BigQuery):\n    @classmethod\n    def type(cls):\n        return \"bigquery_gce\"\n\n    @classmethod\n    def enabled(cls):\n        try:\n            # check if we're on a GCE instance\n            requests.get('http://metadata.google.internal')\n        except requests.exceptions.ConnectionError:\n            return False\n\n        return True\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            'type': 'object',\n            'properties': {\n                'totalMBytesProcessedLimit': {\n                    \"type\": \"number\",\n                    'title': 'Total MByte Processed Limit'\n                },\n                'userDefinedFunctionResourceUri': {\n                    \"type\": \"string\",\n                    'title': 'UDF Source URIs (i.e. gs://bucket/date_utils.js, gs://bucket/string_utils.js )'\n                },\n                'useStandardSql': {\n                    \"type\": \"boolean\",\n                    'title': \"Use Standard SQL (Beta)\",\n                },\n                'location': {\n                    \"type\": \"string\",\n                    \"title\": \"Processing Location\",\n                    \"default\": \"US\",\n                },\n                'loadSchema': {\n                    \"type\": \"boolean\",\n                    \"title\": \"Load Schema\"\n                }\n            }\n        }\n\n    def _get_project_id(self):\n        return requests.get('http://metadata/computeMetadata/v1/project/project-id', headers={'Metadata-Flavor': 'Google'}).content\n\n    def _get_bigquery_service(self):\n        credentials = gce.AppAssertionCredentials(scope='https://www.googleapis.com/auth/bigquery')\n        http = httplib2.Http()\n        http = credentials.authorize(http)\n\n        return build(\"bigquery\", \"v2\", http=http)\n\n\nregister(BigQuery)\nregister(BigQueryGCE)\n", "path": "redash/query_runner/big_query.py"}], "after_files": [{"content": "import datetime\nimport logging\nimport sys\nimport time\nfrom base64 import b64decode\n\nimport httplib2\nimport requests\n\nfrom redash import settings\nfrom redash.query_runner import *\nfrom redash.utils import json_dumps, json_loads\n\nlogger = logging.getLogger(__name__)\n\ntry:\n    import apiclient.errors\n    from apiclient.discovery import build\n    from apiclient.errors import HttpError\n    from oauth2client.service_account import ServiceAccountCredentials\n    from oauth2client.contrib import gce\n\n    enabled = True\nexcept ImportError:\n    enabled = False\n\ntypes_map = {\n    'INTEGER': TYPE_INTEGER,\n    'FLOAT': TYPE_FLOAT,\n    'BOOLEAN': TYPE_BOOLEAN,\n    'STRING': TYPE_STRING,\n    'TIMESTAMP': TYPE_DATETIME,\n}\n\n\ndef transform_row(row, fields):\n    column_index = 0\n    row_data = {}\n\n    for cell in row[\"f\"]:\n        field = fields[column_index]\n        cell_value = cell['v']\n\n        if cell_value is None:\n            pass\n        # Otherwise just cast the value\n        elif field['type'] == 'INTEGER':\n            cell_value = int(cell_value)\n        elif field['type'] == 'FLOAT':\n            cell_value = float(cell_value)\n        elif field['type'] == 'BOOLEAN':\n            cell_value = cell_value.lower() == \"true\"\n        elif field['type'] == 'TIMESTAMP':\n            cell_value = datetime.datetime.fromtimestamp(float(cell_value))\n\n        row_data[field[\"name\"]] = cell_value\n        column_index += 1\n\n    return row_data\n\n\ndef _load_key(filename):\n    f = file(filename, \"rb\")\n    try:\n        return f.read()\n    finally:\n        f.close()\n\n\ndef _get_query_results(jobs, project_id, location, job_id, start_index):\n    query_reply = jobs.getQueryResults(projectId=project_id,\n                                       location=location,\n                                       jobId=job_id,\n                                       startIndex=start_index).execute()\n    logging.debug('query_reply %s', query_reply)\n    if not query_reply['jobComplete']:\n        time.sleep(10)\n        return _get_query_results(jobs, project_id, location, job_id, start_index)\n\n    return query_reply\n\n\nclass BigQuery(BaseQueryRunner):\n    noop_query = \"SELECT 1\"\n\n    @classmethod\n    def enabled(cls):\n        return enabled\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            'type': 'object',\n            'properties': {\n                'projectId': {\n                    'type': 'string',\n                    'title': 'Project ID'\n                },\n                'jsonKeyFile': {\n                    \"type\": \"string\",\n                    'title': 'JSON Key File'\n                },\n                'totalMBytesProcessedLimit': {\n                    \"type\": \"number\",\n                    'title': 'Scanned Data Limit (MB)'\n                },\n                'userDefinedFunctionResourceUri': {\n                    \"type\": \"string\",\n                    'title': 'UDF Source URIs (i.e. gs://bucket/date_utils.js, gs://bucket/string_utils.js )'\n                },\n                'useStandardSql': {\n                    \"type\": \"boolean\",\n                    'title': \"Use Standard SQL\",\n                    \"default\": True,\n                },\n                'location': {\n                    \"type\": \"string\",\n                    \"title\": \"Processing Location\",\n                },\n                'loadSchema': {\n                    \"type\": \"boolean\",\n                    \"title\": \"Load Schema\"\n                },\n                'maximumBillingTier': {\n                    \"type\": \"number\",\n                    \"title\": \"Maximum Billing Tier\"\n                }\n            },\n            'required': ['jsonKeyFile', 'projectId'],\n            \"order\": ['projectId', 'jsonKeyFile', 'loadSchema', 'useStandardSql', 'location', 'totalMBytesProcessedLimit', 'maximumBillingTier', 'userDefinedFunctionResourceUri'],\n            'secret': ['jsonKeyFile']\n        }\n\n    @classmethod\n    def annotate_query(cls):\n        return False\n\n    def _get_bigquery_service(self):\n        scope = [\n            \"https://www.googleapis.com/auth/bigquery\",\n            \"https://www.googleapis.com/auth/drive\"\n        ]\n\n        key = json_loads(b64decode(self.configuration['jsonKeyFile']))\n\n        creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)\n        http = httplib2.Http(timeout=settings.BIGQUERY_HTTP_TIMEOUT)\n        http = creds.authorize(http)\n\n        return build(\"bigquery\", \"v2\", http=http)\n\n    def _get_project_id(self):\n        return self.configuration[\"projectId\"]\n\n    def _get_location(self):\n        return self.configuration.get(\"location\")\n\n    def _get_total_bytes_processed(self, jobs, query):\n        job_data = {\n            \"query\": query,\n            \"dryRun\": True,\n        }\n\n        if self._get_location():\n            job_data['location'] = self._get_location()\n\n        if self.configuration.get('useStandardSql', False):\n            job_data['useLegacySql'] = False\n\n        response = jobs.query(projectId=self._get_project_id(), body=job_data).execute()\n        return int(response[\"totalBytesProcessed\"])\n\n    def _get_query_result(self, jobs, query):\n        project_id = self._get_project_id()\n        job_data = {\n            \"configuration\": {\n                \"query\": {\n                    \"query\": query,\n                }\n            }\n        }\n\n        if self._get_location():\n            job_data['jobReference'] = {\n                'location': self._get_location()\n            }\n\n        if self.configuration.get('useStandardSql', False):\n            job_data['configuration']['query']['useLegacySql'] = False\n\n        if self.configuration.get('userDefinedFunctionResourceUri'):\n            resource_uris = self.configuration[\"userDefinedFunctionResourceUri\"].split(',')\n            job_data[\"configuration\"][\"query\"][\"userDefinedFunctionResources\"] = map(\n                lambda resource_uri: {\"resourceUri\": resource_uri}, resource_uris)\n\n        if \"maximumBillingTier\" in self.configuration:\n            job_data[\"configuration\"][\"query\"][\"maximumBillingTier\"] = self.configuration[\"maximumBillingTier\"]\n\n        insert_response = jobs.insert(projectId=project_id, body=job_data).execute()\n        current_row = 0\n        query_reply = _get_query_results(jobs, project_id=project_id, location=self._get_location(),\n                                         job_id=insert_response['jobReference']['jobId'], start_index=current_row)\n\n        logger.debug(\"bigquery replied: %s\", query_reply)\n\n        rows = []\n\n        while (\"rows\" in query_reply) and current_row < query_reply['totalRows']:\n            for row in query_reply[\"rows\"]:\n                rows.append(transform_row(row, query_reply[\"schema\"][\"fields\"]))\n\n            current_row += len(query_reply['rows'])\n\n            query_result_request = {\n                'projectId': project_id,\n                'jobId': query_reply['jobReference']['jobId'],\n                'startIndex': current_row\n            }\n\n            if self._get_location():\n                query_result_request['location'] = self._get_location()\n\n            query_reply = jobs.getQueryResults(**query_result_request).execute()\n\n        columns = [{'name': f[\"name\"],\n                    'friendly_name': f[\"name\"],\n                    'type': types_map.get(f['type'], \"string\")} for f in query_reply[\"schema\"][\"fields\"]]\n\n        data = {\n            \"columns\": columns,\n            \"rows\": rows,\n            'metadata': {'data_scanned': int(query_reply['totalBytesProcessed'])}\n        }\n\n        return data\n\n    def _get_columns_schema(self, table_data):\n        columns = []\n        for column in table_data['schema']['fields']:\n            columns.extend(self._get_columns_schema_column(column))\n\n        project_id = self._get_project_id()\n        table_name = table_data['id'].replace(\"%s:\" % project_id, \"\")\n        return {'name': table_name, 'columns': columns}\n\n    def _get_columns_schema_column(self, column):\n        columns = []\n        if column['type'] == 'RECORD':\n            for field in column['fields']:\n                columns.append(u\"{}.{}\".format(column['name'], field['name']))\n        else:\n            columns.append(column['name'])\n\n        return columns\n\n    def get_schema(self, get_stats=False):\n        if not self.configuration.get('loadSchema', False):\n            return []\n\n        service = self._get_bigquery_service()\n        project_id = self._get_project_id()\n        datasets = service.datasets().list(projectId=project_id).execute()\n        schema = []\n        for dataset in datasets.get('datasets', []):\n            dataset_id = dataset['datasetReference']['datasetId']\n            tables = service.tables().list(projectId=project_id, datasetId=dataset_id).execute()\n            while True:\n                for table in tables.get('tables', []):\n                    table_data = service.tables().get(projectId=project_id,\n                                                      datasetId=dataset_id,\n                                                      tableId=table['tableReference']['tableId']).execute()\n                    table_schema = self._get_columns_schema(table_data)\n                    schema.append(table_schema)\n\n                next_token = tables.get('nextPageToken', None)\n                if next_token is None:\n                    break\n\n                tables = service.tables().list(projectId=project_id,\n                                               datasetId=dataset_id,\n                                               pageToken=next_token).execute()\n\n        return schema\n\n    def run_query(self, query, user):\n        logger.debug(\"BigQuery got query: %s\", query)\n\n        bigquery_service = self._get_bigquery_service()\n        jobs = bigquery_service.jobs()\n\n        try:\n            if \"totalMBytesProcessedLimit\" in self.configuration:\n                limitMB = self.configuration[\"totalMBytesProcessedLimit\"]\n                processedMB = self._get_total_bytes_processed(jobs, query) / 1000.0 / 1000.0\n                if limitMB < processedMB:\n                    return None, \"Larger than %d MBytes will be processed (%f MBytes)\" % (limitMB, processedMB)\n\n            data = self._get_query_result(jobs, query)\n            error = None\n\n            json_data = json_dumps(data)\n        except apiclient.errors.HttpError as e:\n            json_data = None\n            if e.resp.status == 400:\n                error = json_loads(e.content)['error']['message']\n            else:\n                error = e.content\n        except KeyboardInterrupt:\n            error = \"Query cancelled by user.\"\n            json_data = None\n\n        return json_data, error\n\n\nclass BigQueryGCE(BigQuery):\n    @classmethod\n    def type(cls):\n        return \"bigquery_gce\"\n\n    @classmethod\n    def enabled(cls):\n        try:\n            # check if we're on a GCE instance\n            requests.get('http://metadata.google.internal')\n        except requests.exceptions.ConnectionError:\n            return False\n\n        return True\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            'type': 'object',\n            'properties': {\n                'totalMBytesProcessedLimit': {\n                    \"type\": \"number\",\n                    'title': 'Total MByte Processed Limit'\n                },\n                'userDefinedFunctionResourceUri': {\n                    \"type\": \"string\",\n                    'title': 'UDF Source URIs (i.e. gs://bucket/date_utils.js, gs://bucket/string_utils.js )'\n                },\n                'useStandardSql': {\n                    \"type\": \"boolean\",\n                    'title': \"Use Standard SQL\",\n                    \"default\": True,\n                },\n                'location': {\n                    \"type\": \"string\",\n                    \"title\": \"Processing Location\",\n                    \"default\": \"US\",\n                },\n                'loadSchema': {\n                    \"type\": \"boolean\",\n                    \"title\": \"Load Schema\"\n                }\n            }\n        }\n\n    def _get_project_id(self):\n        return requests.get('http://metadata/computeMetadata/v1/project/project-id', headers={'Metadata-Flavor': 'Google'}).content\n\n    def _get_bigquery_service(self):\n        credentials = gce.AppAssertionCredentials(scope='https://www.googleapis.com/auth/bigquery')\n        http = httplib2.Http()\n        http = credentials.authorize(http)\n\n        return build(\"bigquery\", \"v2\", http=http)\n\n\nregister(BigQuery)\nregister(BigQueryGCE)\n", "path": "redash/query_runner/big_query.py"}]}