search

dssg / tyra

Prediction model evaluation
Other
4 stars 1 forks source link

After running cache_table.sql... #82

Open nanounanue opened 7 years ago

nanounanue commented 7 years ago

Even after running cache_table.sql I got the following errors when try to use tyra

...
food_db         | 2017-08-21T21:20:58.837922331Z ERROR:  syntax error at or near ")" at character 707
food_db         | 2017-08-21T21:20:58.837952550Z STATEMENT:
food_db         | 2017-08-21T21:20:58.837958323Z                SELECT
food_db         | 2017-08-21T21:20:58.837962998Z                model_group_id,
food_db         | 2017-08-21T21:20:58.837967518Z                json_agg((select row_to_json(_)
food_db         | 2017-08-21T21:20:58.837972071Z                        from (select m.model_id,
food_db         | 2017-08-21T21:20:58.837976194Z                                     m.run_time::date,
food_db         | 2017-08-21T21:20:58.837980408Z                                     m.model_comment,
food_db         | 2017-08-21T21:20:58.837984208Z                                     e.value,
food_db         | 2017-08-21T21:20:58.837988093Z                                     e.evaluation_start_time::date)
food_db         | 2017-08-21T21:20:58.837992531Z                                     as _)
food_db         | 2017-08-21T21:20:58.837996499Z                        ORDER BY e.evaluation_start_time
food_db         | 2017-08-21T21:20:58.838000274Z                      ) as series
food_db         | 2017-08-21T21:20:58.838014425Z                FROM results.models as m
food_db         | 2017-08-21T21:20:58.838018568Z                JOIN results.evaluations e using(model_id)
food_db         | 2017-08-21T21:20:58.838022359Z                WHERE evaluation_start_time = train_end_time::timestamp
food_db         | 2017-08-21T21:20:58.838026142Z                AND parameter = '100_abs'
food_db         | 2017-08-21T21:20:58.838029923Z                AND metric = 'precision@'
food_db         | 2017-08-21T21:20:58.838033592Z                AND run_time >= '2017-08-14'
food_db         | 2017-08-21T21:20:58.838042749Z                AND model_group_id in ()
food_db         | 2017-08-21T21:20:58.838045867Z                GROUP BY model_group_id
food_db         | 2017-08-21T21:20:58.838049100Z

Also the stacktrace in python is:

tutorial_tyra   | 2017-08-21T21:28:10.278602622Z Traceback (most recent call last):
tutorial_tyra   | 2017-08-21T21:28:10.278630364Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1836, in __call__
tutorial_tyra   | 2017-08-21T21:28:10.278636885Z     return self.wsgi_app(environ, start_response)
tutorial_tyra   | 2017-08-21T21:28:10.278641641Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1820, in wsgi_app
tutorial_tyra   | 2017-08-21T21:28:10.278646365Z     response = self.make_response(self.handle_exception(e))
tutorial_tyra   | 2017-08-21T21:28:10.278651213Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1403, in handle_exception
tutorial_tyra   | 2017-08-21T21:28:10.278655686Z     reraise(exc_type, exc_value, tb)
tutorial_tyra   | 2017-08-21T21:28:10.278659986Z   File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 33, in reraise
tutorial_tyra   | 2017-08-21T21:28:10.278664317Z     raise value
tutorial_tyra   | 2017-08-21T21:28:10.278668560Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1817, in wsgi_app
tutorial_tyra   | 2017-08-21T21:28:10.278675563Z     response = self.full_dispatch_request()
tutorial_tyra   | 2017-08-21T21:28:10.278681861Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1477, in full_dispatch_request
tutorial_tyra   | 2017-08-21T21:28:10.278686428Z     rv = self.handle_user_exception(e)
tutorial_tyra   | 2017-08-21T21:28:10.278690873Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1381, in handle_user_exception
tutorial_tyra   | 2017-08-21T21:28:10.278697360Z     reraise(exc_type, exc_value, tb)
tutorial_tyra   | 2017-08-21T21:28:10.278701467Z   File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 33, in reraise
tutorial_tyra   | 2017-08-21T21:28:10.278705802Z     raise value
tutorial_tyra   | 2017-08-21T21:28:10.278709886Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1475, in full_dispatch_request
tutorial_tyra   | 2017-08-21T21:28:10.278714216Z     rv = self.dispatch_request()
tutorial_tyra   | 2017-08-21T21:28:10.278718243Z   File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1461, in dispatch_request
tutorial_tyra   | 2017-08-21T21:28:10.278722570Z     return self.view_functions[rule.endpoint](**req.view_args)
tutorial_tyra   | 2017-08-21T21:28:10.278726693Z   File "/tyra-master/webapp/controller.py", line 107, in get_model_groups
tutorial_tyra   | 2017-08-21T21:28:10.278733135Z     output = query.get_model_groups(query_arg)
tutorial_tyra   | 2017-08-21T21:28:10.278740010Z   File "/tyra-master/webapp/query.py", line 121, in get_model_groups
tutorial_tyra   | 2017-08-21T21:28:10.278746609Z     con=db.engine)
tutorial_tyra   | 2017-08-21T21:28:10.278752193Z   File "/usr/local/lib/python3.6/site-packages/pandas/io/sql.py", line 515, in read_sql
tutorial_tyra   | 2017-08-21T21:28:10.278756657Z     chunksize=chunksize)
tutorial_tyra   | 2017-08-21T21:28:10.278760616Z   File "/usr/local/lib/python3.6/site-packages/pandas/io/sql.py", line 1190, in read_query
tutorial_tyra   | 2017-08-21T21:28:10.278774447Z     result = self.execute(*args)
tutorial_tyra   | 2017-08-21T21:28:10.278778255Z   File "/usr/local/lib/python3.6/site-packages/pandas/io/sql.py", line 1081, in execute
tutorial_tyra   | 2017-08-21T21:28:10.278782004Z     return self.connectable.execute(*args, **kwargs)
tutorial_tyra   | 2017-08-21T21:28:10.278785566Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1991, in execute
tutorial_tyra   | 2017-08-21T21:28:10.278791113Z     return connection.execute(statement, *multiparams, **params)
tutorial_tyra   | 2017-08-21T21:28:10.278794910Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 906, in execute
tutorial_tyra   | 2017-08-21T21:28:10.278799147Z     return self._execute_text(object, multiparams, params)
tutorial_tyra   | 2017-08-21T21:28:10.278805080Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
tutorial_tyra   | 2017-08-21T21:28:10.278811557Z     statement, parameters
tutorial_tyra   | 2017-08-21T21:28:10.278820344Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
tutorial_tyra   | 2017-08-21T21:28:10.278826563Z     context)
tutorial_tyra   | 2017-08-21T21:28:10.278831703Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
tutorial_tyra   | 2017-08-21T21:28:10.278837525Z     exc_info
tutorial_tyra   | 2017-08-21T21:28:10.278843186Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
tutorial_tyra   | 2017-08-21T21:28:10.278849140Z     reraise(type(exception), exception, tb=exc_tb, cause=cause)
tutorial_tyra   | 2017-08-21T21:28:10.278854044Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 185, in reraise
tutorial_tyra   | 2017-08-21T21:28:10.278859721Z     raise value.with_traceback(tb)
tutorial_tyra   | 2017-08-21T21:28:10.278864433Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
tutorial_tyra   | 2017-08-21T21:28:10.278870158Z     context)
tutorial_tyra   | 2017-08-21T21:28:10.278875656Z   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
tutorial_tyra   | 2017-08-21T21:28:10.278882016Z     cursor.execute(statement, parameters)
tutorial_tyra   | 2017-08-21T21:28:10.278887857Z sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ")"
tutorial_tyra   | 2017-08-21T21:28:10.278894390Z LINE 19:         AND model_group_id in ()
tutorial_tyra   | 2017-08-21T21:28:10.278900216Z                                         ^
tutorial_tyra   | 2017-08-21T21:28:10.278907607Z  [SQL: '\n        SELECT\n        model_group_id,\n        json_agg((select row_to_json(_)\n                from (select m.model_id,\n                             m.run_time::date,\n                             m.model_comment,\n                             e.value,\n                             e.evaluation_start_time::date)\n                             as _)\n                ORDER BY e.evaluation_start_time\n              ) as series\n        FROM results.models as m\n        JOIN results.evaluations e using(model_id)\n        WHERE evaluation_start_time = train_end_time::timestamp\n        AND parameter = %(parameter)s\n        AND metric = %(metric)s\n        AND run_time >= %(runtime)s\n        AND model_group_id in ()\n        GROUP BY model_group_id\n        '] [parameters: {'parameter': '100_abs', 'metric': 'precision@', 'runtime': '2017-08-14'}]
tweddielin commented 7 years ago

@nanounanue Does the ranked_table exist?

tweddielin commented 7 years ago

Found the issue that the parameter and metric are hard-coded.

https://github.com/dssg/tyra/blob/deaaabc599ee3caa057c223216c2bee33c861f19/cache_table.sql#L20

The reason I did this is to filter out millions of model before ranking the models. Since this query is also a temporary solution, once "audition" is done, we don't rank millions of model anymore.