optimizer_api.py 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382
  1. #!/usr/bin/env python
  2. # Licensed to Cloudera, Inc. under one
  3. # or more contributor license agreements. See the NOTICE file
  4. # distributed with this work for additional information
  5. # regarding copyright ownership. Cloudera, Inc. licenses this file
  6. # to you under the Apache License, Version 2.0 (the
  7. # "License"); you may not use this file except in compliance
  8. # with the License. You may obtain a copy of the License at
  9. #
  10. # http://www.apache.org/licenses/LICENSE-2.0
  11. #
  12. # Unless required by applicable law or agreed to in writing, software
  13. # distributed under the License is distributed on an "AS IS" BASIS,
  14. # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  15. # See the License for the specific language governing permissions and
  16. # limitations under the License.
  17. import json
  18. import logging
  19. import random
  20. from django.http import Http404
  21. from django.utils.translation import ugettext as _
  22. from django.views.decorators.http import require_POST
  23. from desktop.lib.django_util import JsonResponse
  24. from desktop.lib.i18n import force_unicode
  25. from desktop.models import Document2
  26. from notebook.models import Notebook
  27. from metadata.optimizer_client import OptimizerApi
  28. from metadata.conf import OPTIMIZER
  29. LOG = logging.getLogger(__name__)
  30. def error_handler(view_fn):
  31. def decorator(*args, **kwargs):
  32. try:
  33. return view_fn(*args, **kwargs)
  34. except Http404, e:
  35. raise e
  36. except Exception, e:
  37. LOG.exception(e)
  38. response = {
  39. 'status': -1,
  40. 'message': force_unicode(str(e))
  41. }
  42. return JsonResponse(response, status=500)
  43. return decorator
  44. @require_POST
  45. @error_handler
  46. def top_tables(request):
  47. response = {'status': -1}
  48. database = request.POST.get('database', 'default')
  49. len = request.POST.get('len', 1000)
  50. if OPTIMIZER.MOCKING.get():
  51. from beeswax.server import dbms
  52. from beeswax.server.dbms import get_query_server_config
  53. db = dbms.get(request.user)
  54. tables = [
  55. {'name': table, 'popularity': random.randint(1, 100) , 'column_count': random.randint(1, 100), 'is_fact': bool(random.getrandbits(1))}
  56. for table in db.get_tables(database=database)
  57. ][:len]
  58. else:
  59. """
  60. Get back:
  61. # u'details': [{u'columnCount': 28, u'name': u'date_dim', u'patternCount': 136, u'workloadPercent': 89, u'total': 92, u'type': u'Dimension', u'eid': u'19'},
  62. """
  63. api = OptimizerApi()
  64. data = api.top_tables()
  65. tables = [{
  66. 'eid': table['eid'],
  67. 'name': table['name'],
  68. 'popularity': table['workloadPercent'],
  69. 'column_count': table['columnCount'],
  70. 'patternCount': table['patternCount'],
  71. 'total': table['total'],
  72. 'is_fact': table['type'] != 'Dimension'
  73. } for table in data['details']
  74. ]
  75. response['top_tables'] = tables
  76. response['status'] = 0
  77. return JsonResponse(response)
  78. @require_POST
  79. @error_handler
  80. def table_details(request):
  81. response = {'status': -1}
  82. table_name = request.POST.get('tableName')
  83. api = OptimizerApi()
  84. data = api.table_details(table_name=table_name)
  85. if data['status'] == 'success':
  86. response['status'] = 0
  87. response['details'] = data['details']
  88. print [c['columnName'] for c in data['details']['sortedTotal']]
  89. else:
  90. response['message'] = 'Optimizer: %s' % data['details']
  91. return JsonResponse(response)
  92. @require_POST
  93. @error_handler
  94. def query_compatibility(request):
  95. response = {'status': -1}
  96. source_platform = request.POST.get('sourcePlatform')
  97. target_platform = request.POST.get('targetPlatform')
  98. query = request.POST.get('query')
  99. api = OptimizerApi()
  100. data = api.query_compatibility(source_platform=source_platform, target_platform=target_platform, query=query)
  101. if data['status'] == 'success':
  102. response['status'] = 0
  103. response['query_compatibility'] = json.loads(data['details'])
  104. else:
  105. response['message'] = 'Optimizer: %s' % data['details']
  106. return JsonResponse(response)
  107. # Mocked
  108. @require_POST
  109. @error_handler
  110. def query_complexity(request):
  111. response = {'status': -1}
  112. snippet = json.loads(request.POST.get('snippet'))
  113. if 'select * from tsqc_date t join atd_au_dtl a on (t.date = a.date)' in snippet['statement'].lower():
  114. comment = 'Large join is happening'
  115. elif 'large' in snippet['statement'].lower():
  116. comment = 'Previously failed 5 times in a row'
  117. elif 'partition' in snippet['statement'].lower():
  118. comment = 'Has 50k partitions'
  119. else:
  120. comment = ''
  121. response['query_complexity'] = {
  122. 'level': random.choice(['LOW', 'MEDIUM', 'HIGH']),
  123. 'comment': comment
  124. }
  125. response['status'] = 0
  126. return JsonResponse(response)
  127. @require_POST
  128. @error_handler
  129. def popular_values(request):
  130. response = {'status': -1}
  131. table_name = request.POST.get('tableName')
  132. column_name = request.POST.get('columnName')
  133. if OPTIMIZER.MOCKING.get():
  134. if column_name:
  135. values = [
  136. {
  137. "values": [
  138. "1",
  139. "(6,0)"
  140. ],
  141. "columnName": "d_dow",
  142. "tableName": "date_dim"
  143. }
  144. ]
  145. else:
  146. values = [
  147. {
  148. "values": [
  149. "('2001q1','2001q2','2001q3')",
  150. "'2001q1'"
  151. ],
  152. "columnName": "d_quarter_name",
  153. "tableName": "date_dim"
  154. },
  155. {
  156. "values": [
  157. "1",
  158. "2",
  159. "4"
  160. ],
  161. "columnName": "d_qoy",
  162. "tableName": "date_dim"
  163. },
  164. {
  165. "values": [
  166. "Subquery"
  167. ],
  168. "columnName": "d_week_seq",
  169. "tableName": "date_dim"
  170. },
  171. {
  172. "values": [
  173. "(cast('1998-08-14' as date) + interval '30' day)",
  174. "(cast ('1998-03-08' as date) + interval '30' day)",
  175. "d1.d_date + 5",
  176. "cast('1998-08-14' as date)",
  177. "cast('1999-04-26' as date)",
  178. "'2002-4-01'",
  179. "(cast('2000-02-02' as date) + interval '90' day)",
  180. "(cast('2002-4-01' as date) + interval '60' day)",
  181. "(cast('2002-01-18' as date) + 60 + interval '60' day)",
  182. "('1999-04-17','1999-10-04','1999-11-10')",
  183. "(cast('1999-04-26' as date) + 30 + interval '30' day)",
  184. "(cast('1999-06-03' as date) + interval '30' day)",
  185. "cast('1998-01-06' as date)",
  186. "(cast('2000-2-01' as date) + interval '60' day)",
  187. "(cast('2002-04-01' as date) + interval '30' day)",
  188. "( cast('2000-03-22' as date ) + interval '90' day )",
  189. "cast('2001-08-21' as date)",
  190. "(cast ('1998-03-08' as date) - interval '30' day)",
  191. "'2000-03-22'",
  192. "(cast('2001-08-21' as date) + interval '14' day)",
  193. "( cast('1999-08-25' as date) + interval '30' day )",
  194. "Subquery",
  195. "'2000-3-01'",
  196. "cast('2002-01-18' as date)",
  197. "(cast ('2001-03-14' as date) - interval '30' day)",
  198. "'2000-02-02'",
  199. "cast('2002-04-01' as date)",
  200. "'2002-03-09'",
  201. "(cast('2000-3-01' as date) + interval '60' day)",
  202. "cast('1999-06-03' as date)",
  203. "cast('1999-08-25' as date)",
  204. "(cast ('2001-03-14' as date) + interval '30' day)",
  205. "'2000-2-01'",
  206. "(cast('1998-01-06' as date) + interval '60' day)"
  207. ],
  208. "columnName": "d_date",
  209. "tableName": "date_dim"
  210. },
  211. {
  212. "values": [
  213. "1223",
  214. "1200",
  215. "1202",
  216. "1214+11",
  217. "(select distinct date_dim.d_month_seq+1 from date_dim where date_dim.d_year = 2001 and date_dim.d_moy = 5)",
  218. "1181+11",
  219. "1199",
  220. "1191",
  221. "(1206,1206+1,1206+2,1206+3,1206+4,1206+5,1206+6,1206+7,1206+8,1206+9,1206+10,1206+11)",
  222. "1211 + 11",
  223. "1199 + 11",
  224. "1212",
  225. "(select distinct date_dim.d_month_seq+3 from date_dim where date_dim.d_year = 2001 and date_dim.d_moy = 5)",
  226. "1211",
  227. "1214",
  228. "Subquery",
  229. "(1195,1195+1,1195+2,1195+3,1195+4,1195+5,1195+6,1195+7,1195+8,1195+9,1195+10,1195+11)",
  230. "1200+11",
  231. "1212 + 11",
  232. "1223+11",
  233. "1183 + 11",
  234. "1183",
  235. "1181",
  236. "1191 + 11",
  237. "1202 + 11"
  238. ],
  239. "columnName": "d_month_seq",
  240. "tableName": "date_dim"
  241. },
  242. {
  243. "values": [
  244. "11",
  245. "4 + 3",
  246. "12",
  247. "3+2",
  248. "2+3",
  249. "1",
  250. "3",
  251. "2",
  252. "5",
  253. "4",
  254. "6",
  255. "8",
  256. "10"
  257. ],
  258. "columnName": "d_moy",
  259. "tableName": "date_dim"
  260. },
  261. {
  262. "values": [
  263. "25",
  264. "16",
  265. "28",
  266. "1",
  267. "3",
  268. "2"
  269. ],
  270. "columnName": "d_dom",
  271. "tableName": "date_dim"
  272. },
  273. {
  274. "values": [
  275. "(1998,1998+1)",
  276. "2000 + 1",
  277. "2000 + 2",
  278. "(2000,2000+1,2000+2)",
  279. "(1999,1999+1,1999+2)",
  280. "2000-1",
  281. "2001+1",
  282. "1999 + 2",
  283. "2000+1",
  284. "2000+2",
  285. "1999+1",
  286. "(2002)",
  287. "( 1999, 1999 + 1, 1999 + 2, 1999 + 3 )",
  288. "1999-1",
  289. "( 1998, 1998 + 1, 1998 + 2 )",
  290. "1999",
  291. "1998",
  292. "(1998,1998+1,1998+2)",
  293. "2002",
  294. "2000",
  295. "2001",
  296. "2004"
  297. ],
  298. "columnName": "d_year",
  299. "tableName": "date_dim"
  300. },
  301. {
  302. "values": [
  303. "1",
  304. "(6,0)"
  305. ],
  306. "columnName": "d_dow",
  307. "tableName": "date_dim"
  308. }
  309. ]
  310. else:
  311. api = OptimizerApi()
  312. data = api.popular_filter_values(table_name=table_name, column_name=column_name)
  313. if data['status'] == 'success':
  314. if 'status' in data['details']:
  315. response['values'] = [] # Bug in Opt API
  316. else:
  317. response['values'] = data['details']
  318. response['status'] = 0
  319. else:
  320. response['message'] = 'Optimizer: %s' % data['details']
  321. return JsonResponse(response)
  322. @require_POST
  323. @error_handler
  324. def upload_history(request):
  325. response = {'status': -1}
  326. query_type = 'hive'
  327. queries = [
  328. (doc.uuid, 1000, Notebook(document=doc).get_data()['snippets'][0]['statement'])
  329. for doc in Document2.objects.get_history(doc_type='query-%s' % query_type, user=request.user)[:25]
  330. ]
  331. api = OptimizerApi()
  332. response['upload_history'] = api.upload(queries=queries, source_platform=query_type)
  333. response['status'] = 0
  334. return JsonResponse(response)