def test_type_merged_with_ids(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE _type = 'symbol' AND _id = '1'")
self.assertEqual({'query': {'ids': {'value': ['1'], 'type': 'symbol'}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE _type = 'symbol' AND _id IN ('1', '2')")
self.assertEqual({'query': {'ids': {'value': ['1', '2'], 'type': 'symbol'}}}, executor.request)
executor = es_query.create_executor("SELECT * FROM symbol WHERE _id IN ('1', '2') AND _type = 'symbol'")
self.assertEqual({'query': {'ids': {'value': ['1', '2'], 'type': 'symbol'}}}, executor.request)
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _id IN ('1', '2') AND _type = 'symbol' AND _type='abc'")
self.assertEqual({'query': {
'bool': {'filter': [{'ids': {'type': 'symbol', 'value': ['1', '2']}}, {'type': {'value': 'abc'}}]}}},
executor.request)
def test_type_merged_with_ids(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _type = 'symbol' AND _id = '1'")
self.assertEqual(
{'query': {
'ids': {
'value': ['1'],
'type': 'symbol'
}
}}, executor.request)
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _type = 'symbol' AND _id IN ('1', '2')"
)
self.assertEqual(
{'query': {
'ids': {
'value': ['1', '2'],
'type': 'symbol'
}
}}, executor.request)
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _id IN ('1', '2') AND _type = 'symbol'"
)
self.assertEqual(
{'query': {
'ids': {
'value': ['1', '2'],
'type': 'symbol'
}
}}, executor.request)
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _id IN ('1', '2') AND _type = 'symbol' AND _type='abc'"
)
self.assertEqual(
{
'query': {
'bool': {
'filter': [{
'ids': {
'type': 'symbol',
'value': ['1', '2']
}
}, {
'type': {
'value': 'abc'
}
}]
}
}
}, executor.request)
def test_having_by_count(self):
executor = es_query.create_executor(
"SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol GROUP BY ipo_year HAVING ipo_count > 100"
)
self.assertEqual(
{
'aggs': {
u'ipo_year': {
'terms': {
'field': u'ipo_year',
'size': 0
},
'aggs': {
'having': {
'bucket_selector': {
'buckets_path': {
u'ipo_count': '_count'
},
'script': {
'lang': 'expression',
'inline': u' ipo_count > 100'
}
}
}
}
}
},
'size': 0
}, executor.request)
def test_select_system_field(self):
executor = es_query.create_executor(
'SELECT _id, _type, _index FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"exchange": "nasdaq"
},
"_index": "symbol"
}],
"total":
6714,
"max_score":
1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{
'_id': 'AVLgXwu88_EnCX8dV9PN',
'_type': 'symbol',
'_index': 'symbol'
}], rows)
def test_one_level(self):
executor = es_query.create_executor([
"WITH SELECT MAX(sum_this_year) AS max_all_times FROM symbol AS all_symbols",
"SELECT ipo_year, SUM(market_cap) AS sum_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"
])
self.assertEqual(
{
'aggs': {
u'max_all_times': {
u'max_bucket': {
'buckets_path': u'ipo_year.sum_this_year'
}
},
u'ipo_year': {
'terms': {
'field': u'ipo_year',
'size': 5
},
'aggs': {
u'sum_this_year': {
u'sum': {
'field': u'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_group_by_date_trunc(self):
executor = es_query.create_executor(
"SELECT year, MAX(adj_close) FROM quote WHERE symbol='AAPL' "
"GROUP BY TO_CHAR(date_trunc('year',\"date\"), '%Y-%m-%d') AS year"
)
self.assertEqual(
{
'query': {
'term': {
'symbol': 'AAPL'
}
},
'aggs': {
'year': {
'date_histogram': {
'field': 'date',
'interval': 'year',
'time_zone': '+08:00',
'format': 'yyyy-MM-dd'
},
'aggs': {
'MAX(adj_close)': {
'max': {
'field': 'adj_close'
}
}
}
}
},
'size': 0
}, executor.request)
def test_select_nested_field(self):
executor = es_query.create_executor('SELECT "a.exchange" FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [
{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"a": {
"exchange": "nasdaq"
}
},
"_index": "symbol"
}
],
"total": 6714,
"max_score": 1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{
'a.exchange': 'nasdaq'}],
rows)
def test_filter_without_group_by(self):
executor = es_query.create_executor([
"WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols",
"SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000"
])
self.assertEqual(
{
'aggs': {
'level2': {
'filter': {
'term': {
u'ipo_year': 2000
}
},
'aggs': {
u'max_at_2000': {
u'max': {
'field': u'market_cap'
}
}
}
},
u'max_all_times': {
u'max': {
'field': u'market_cap'
}
}
},
'size': 0
}, executor.request)
def test_join_on_one_field(self):
executor = es_query.create_executor([
"WITH SELECT * FROM symbol WHERE sector='Finance' AS finance_symbols",
'SELECT * FROM quote JOIN finance_symbols ON quote.symbol = finance_symbols.symbol'
])
self.assertEqual(
{
'query': {
'bool': {
'filter': [{}, {
'filterjoin': {
u'symbol': {
'indices': u'symbol*',
'path': u'symbol',
'query': {
'term': {
u'sector': 'Finance'
}
}
}
}
}]
}
}
}, executor.request)
def test_or_not(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE exchange='nyse' OR NOT sector='Technology'")
self.assertEqual(
{'query': {'bool': {'should': [
{'term': {'exchange': 'nyse'}},
{'bool': {'must_not': [{'term': {'sector': 'Technology'}}]}}]}}},
executor.request)
def test_and_and(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND sector='Technology' AND ipo_year=1998"
)
self.assertEqual(
{
'query': {
'bool': {
'filter': [
{
'term': {
'exchange': 'nyse'
}
},
{
'term': {
'sector': 'Technology'
}
},
{
'term': {
'ipo_year': 1998
}
},
]
}
}
}, executor.request)
def test_and_or_must_use_parentheses(self):
try:
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND sector='Technology' OR ipo_year > 1998")
except:
return
self.fail('should fail')
def test_order_by_histogram(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, MAX(market_cap) AS max_market_cap FROM symbol "
"GROUP BY histogram(ipo_year, 3) AS ipo_year_range ORDER BY ipo_year_range LIMIT 2"
)
self.assertEqual(
{
'aggs': {
'ipo_year_range': {
'aggs': {
'max_market_cap': {
'max': {
'field': 'market_cap'
}
}
},
'histogram': {
'field': 'ipo_year',
'interval': 3,
'order': {
'_key': 'asc'
},
'size': 2
}
}
},
'size': 0
}, executor.request)
def test_order_by_metric(self):
executor = es_query.create_executor(
"SELECT ipo_year, MAX(market_cap) AS c FROM symbol GROUP BY ipo_year ORDER BY c"
)
self.assertEqual(
{
'aggs': {
'ipo_year': {
'terms': {
'field': 'ipo_year',
'order': {
'c': 'asc'
},
'size': 0
},
'aggs': {
'c': {
'max': {
'field': 'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_having_by_metric(self):
executor = es_query.create_executor(
"SELECT ipo_year, MAX(market_cap) AS max_market_cap FROM symbol GROUP BY ipo_year HAVING max_market_cap > 100"
)
self.assertEqual(
{
'aggs': {
'ipo_year': {
'terms': {
'field': 'ipo_year',
'size': 0
},
'aggs': {
'having': {
'bucket_selector': {
'buckets_path': {
'max_market_cap': 'max_market_cap'
},
'script': {
'lang': 'expression',
'inline': ' max_market_cap > 100'
}
}
},
'max_market_cap': {
'max': {
'field': 'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_group_by_numeric_range(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY CASE "
" WHEN ipo_year_range >= 2000 THEN 'post_2000' "
" WHEN ipo_year_range < 2000 THEN 'pre_2000' END AS ipo_year_range"
)
self.assertEqual(
{
'aggs': {
'ipo_year_range': {
'range': {
'ranges': [{
'from': 2000.0,
'key': 'post_2000'
}, {
'to': 2000.0,
'key': 'pre_2000'
}],
'field':
'ipo_year_range'
},
'aggs': {}
}
},
'size': 0
}, executor.request)
def test_and_or_used_parentheses(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND (sector='Technology' OR ipo_year > 1998)"
)
self.assertEqual(
{
'query': {
'bool': {
'filter': [{
'term': {
'exchange': 'nyse'
}
}, {
'bool': {
'should': [{
'term': {
'sector': 'Technology'
}
}, {
'range': {
'ipo_year': {
'gt': 1998.0
}
}
}]
}
}]
}
}
}, executor.request)
def test_group_by_filters(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY CASE "
" WHEN ipo_year_range > 2000 THEN 'post_2000' "
" WHEN ipo_year_range < 2000 THEN 'pre_2000'"
" ELSE '2000' END AS ipo_year_range")
self.assertEqual(
{
'aggs': {
'ipo_year_range': {
'filters': {
'filters': {
'pre_2000': {
'range': {
'ipo_year_range': {
'lt': 2000
}
}
},
'post_2000': {
'range': {
'ipo_year_range': {
'gt': 2000
}
}
}
},
'other_bucket_key': '2000'
},
'aggs': {}
}
},
'size': 0
}, executor.request)
def test_order_by_metric(self):
executor = es_query.create_executor(
"SELECT ipo_year, MAX(market_cap) AS c FROM symbol GROUP BY ipo_year ORDER BY c")
self.assertEqual(
{'aggs': {'ipo_year': {'terms': {'field': 'ipo_year', 'order': {'c': 'asc'}, 'size': 0},
'aggs': {'c': {'max': {'field': 'market_cap'}}}}}, 'size': 0},
executor.request)
def test_csum(self):
executor = es_query.create_executor([
"SELECT year, MAX(adj_close) AS max_adj_close, CSUM(max_adj_close) FROM quote "
"WHERE symbol='AAPL' GROUP BY date_trunc('year', \"date\") AS year"
])
self.assertEqual(
{
'query': {
'term': {
u'symbol': 'AAPL'
}
},
'aggs': {
u'year': {
'date_histogram': {
'field': u'date',
'interval': 'year',
'time_zone': '+08:00'
},
'aggs': {
u'max_adj_close': {
u'max': {
'field': u'adj_close'
}
},
'CSUM(max_adj_close)': {
'cumulative_sum': {
'buckets_path': u'max_adj_close'
}
}
}
}
},
'size': 0
}, executor.request)
def test_group_by_histogram(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY histogram(ipo_year, 5) AS ipo_year_range")
self.assertEqual(
{'aggs': {'ipo_year_range': {'aggs': {}, 'histogram': {'field': 'ipo_year', 'interval': 5}}}, 'size': 0},
executor.request)
def test_select_expression(self):
executor = es_query.create_executor('SELECT "a.price"/2 FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"a": {
"price": 100
}
},
"_index": "symbol"
}],
"total":
6714,
"max_score":
1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{'"a.price"/2': 50}], rows)
def test_drill_down_one_direction(self):
executor = es_query.create_executor([
"WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols",
"SELECT ipo_year, MAX(market_cap) AS max_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"
])
self.assertEqual(
{
'aggs': {
'max_all_times': {
'max': {
'field': 'market_cap'
}
},
'ipo_year': {
'terms': {
'field': 'ipo_year',
'size': 5
},
'aggs': {
'max_this_year': {
'max': {
'field': 'market_cap'
}
}
}
}
},
'size': 0
}, executor.request)
def test_select_expression(self):
executor = es_query.create_executor('SELECT "a.price"/2 FROM symbol')
self.assertEqual({}, executor.request)
rows = executor.select_response({
"hits": {
"hits": [
{
"_score": 1.0,
"_type": "symbol",
"_id": "AVLgXwu88_EnCX8dV9PN",
"_source": {
"a": {
"price": 100
}
},
"_index": "symbol"
}
],
"total": 6714,
"max_score": 1.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 32,
"timed_out": False
})
self.assertEqual([{
'"a.price"/2': 50}],
rows)
def test_sum_of_squares(self):
executor = es_query.create_executor("SELECT sum_of_squares(last_sale), std_deviation(last_sale) FROM symbol")
rows = executor.select_response({
"hits": {
"hits": [],
"total": 6714,
"max_score": 0.0
},
"_shards": {
"successful": 3,
"failed": 0,
"total": 3
},
"took": 5,
"aggregations": {
"last_sale_extended_stats": {
"count": 6634,
"min": 0.0,
"sum_of_squares": 320576400178.0,
"max": 269500.0,
"sum": 17407390.0,
"std_deviation": 6437.239059099383,
"std_deviation_bounds": {
"upper": 15498.444051270819,
"lower": -10250.512185126712
},
"variance": 41438046.703994706,
"avg": 2623.965933072053
}
},
"timed_out": False
})
self.assertEqual([
{'sum_of_squares(last_sale)': 320576400178.0, 'std_deviation(last_sale)': 6437.239059099383}],
rows)
def test_having_by_count(self):
executor = es_query.create_executor(
"SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol GROUP BY ipo_year HAVING ipo_count > 100")
self.assertEqual(
{'aggs': {u'ipo_year': {'terms': {'field': u'ipo_year', 'size': 0}, 'aggs': {'having': {
'bucket_selector': {'buckets_path': {u'ipo_count': '_count'},
'script': {'lang': 'expression', 'inline': u' ipo_count > 100'}}}}}}, 'size': 0},
executor.request)
def test_order_by_term(self):
executor = es_query.create_executor(
"SELECT ipo_year, COUNT(*) FROM symbol GROUP BY ipo_year ORDER BY ipo_year")
self.assertEqual(
{'aggs': {
'ipo_year': {'terms': {'field': 'ipo_year', 'order': {'_term': 'asc'}, 'size': 0}, 'aggs': {}}},
'size': 0},
executor.request)
def test_group_by_two(self):
executor = es_query.create_executor("SELECT ipo_year, COUNT(*) FROM symbol GROUP BY ipo_year, abc")
self.assertEqual(
{'aggs': {
'ipo_year': {'terms': {'field': 'ipo_year', 'size': 0}, 'aggs': {
'abc': {'terms': {'field': 'abc', 'size': 0}, 'aggs': {}}}}},
'size': 0},
executor.request)
def test_dot_field(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE 'nyse'=\"a.exchange\"")
self.assertEqual({'query': {
'term': {
'a.exchange': 'nyse'
}
}}, executor.request)
def test_in(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE symbol IN ('AAPL', 'GOOG')")
self.assertEqual({'query': {
'terms': {
u'symbol': ['AAPL', 'GOOG']
}
}}, executor.request)
def test_and_or_must_use_parentheses(self):
try:
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND sector='Technology' OR ipo_year > 1998"
)
except:
return
self.fail('should fail')
def test_id_in(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _id IN ('1', '2')")
self.assertEqual({'query': {
'ids': {
'value': ['1', '2']
}
}}, executor.request)
def test_type_eq(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE _type = 'symbol'")
self.assertEqual({'query': {
'type': {
'value': 'symbol'
}
}}, executor.request)
def test_group_by_single_value_script(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol GROUP BY ipo_year / 6 AS ipo_year_range")
self.assertEqual(
{'aggs': {'ipo_year_range': {
'terms': {'field': 'ipo_year', 'size': 0, 'script': {'lang': 'expression', 'inline': '_value / 6'}},
'aggs': {}}}, 'size': 0},
executor.request)
def test_field_eq_string(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse'")
self.assertEqual({'query': {
'term': {
'exchange': 'nyse'
}
}}, executor.request)
def test_field_in_range_not_merged(self):
executor = es_query.create_executor("SELECT * FROM symbol WHERE last_sale > 500 AND last_sale > 600")
self.assertEqual(
{'query': {'bool': {'filter': [
{'range': {'last_sale': {'gt': 500.0}}},
{'range': {'last_sale': {'gt': 600.0}}}]
}}},
executor.request)
def test_is_not_null(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE last_sale IS NOT NULL")
self.assertEqual({'query': {
'exists': {
'field': 'last_sale'
}
}}, executor.request)
def test_field_can_be_right_operand(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE 'nyse'=exchange")
self.assertEqual({'query': {
'term': {
'exchange': 'nyse'
}
}}, executor.request)
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE 1998<ipo_year")
self.assertEqual({'query': {
'range': {
'ipo_year': {
'gte': 1998.0
}
}
}}, executor.request)
def test_order_by_histogram(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, MAX(market_cap) AS max_market_cap FROM symbol "
"GROUP BY histogram(ipo_year, 3) AS ipo_year_range ORDER BY ipo_year_range LIMIT 2")
self.assertEqual(
{'aggs': {'ipo_year_range': {'aggs': {'max_market_cap': {'max': {'field': 'market_cap'}}},
'histogram': {'field': 'ipo_year', 'interval': 3, 'order': {'_key': 'asc'},
'size': 2}}}, 'size': 0},
executor.request)
def test_drill_down_one_direction(self):
executor = es_query.create_executor([
"WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols",
"SELECT ipo_year, MAX(market_cap) AS max_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"])
self.assertEqual(
{'aggs': {'max_all_times': {'max': {'field': 'market_cap'}},
'ipo_year': {'terms': {'field': 'ipo_year', 'size': 5},
'aggs': {'max_this_year': {'max': {'field': 'market_cap'}}}}}, 'size': 0},
executor.request)
def test_one_level(self):
executor = es_query.create_executor([
"WITH SELECT MAX(sum_this_year) AS max_all_times FROM symbol AS all_symbols",
"SELECT ipo_year, SUM(market_cap) AS sum_this_year FROM all_symbols GROUP BY ipo_year LIMIT 5"])
self.assertEqual(
{'aggs': {u'max_all_times': {u'max_bucket': {'buckets_path': u'ipo_year.sum_this_year'}},
u'ipo_year': {'terms': {'field': u'ipo_year', 'size': 5},
'aggs': {u'sum_this_year': {u'sum': {'field': u'market_cap'}}}}}, 'size': 0},
executor.request)
def test_group_by_date_trunc(self):
executor = es_query.create_executor(
"SELECT year, MAX(adj_close) FROM quote WHERE symbol='AAPL' "
"GROUP BY date_trunc('year',\"date\") AS year")
self.assertEqual(
{'query': {'term': {'symbol': 'AAPL'}}, 'aggs': {
'year': {'date_histogram': {'field': 'date', 'interval': 'year', 'time_zone': '+08:00'},
'aggs': {'MAX(adj_close)': {'max': {'field': 'adj_close'}}}}}, 'size': 0},
executor.request)
def test_filter_then_group_by(self):
executor = es_query.create_executor([
"WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols",
"WITH SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000 AS year_2000",
"SELECT sector, MAX(market_cap) AS max_per_sector FROM year_2000 GROUP BY sector LIMIT 2"])
rows = executor.select_response({
"hits": {
"hits": [],
"total": 6714,
"max_score": 0.0
},
"_shards": {
"successful": 1,
"failed": 0,
"total": 1
},
"took": 5,
"aggregations": {
"year_2000": {
"max_at_2000": {
"value": 20310000000.0
},
"sector": {
"buckets": [
{
"max_per_sector": {
"value": 19600000000.0
},
"key": "Health Care",
"doc_count": 18
},
{
"max_per_sector": {
"value": 4440000000.0
},
"key": "Technology",
"doc_count": 16
}
],
"sum_other_doc_count": 24,
"doc_count_error_upper_bound": 0
},
"doc_count": 58
},
"max_all_times": {
"value": 522690000000.0
}
},
"timed_out": False
})
self.assertEqual(
[{"sector": "Health Care", "max_all_times": 522690000000.0, "max_at_2000": 20310000000.0,
"max_per_sector": 19600000000.0, "_bucket_path": ["year_2000", "level3"]},
{"sector": "Technology", "max_all_times": 522690000000.0, "max_at_2000": 20310000000.0,
"max_per_sector": 4440000000.0, "_bucket_path": ["year_2000", "level3"]}],
rows)
def test_and_and(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND sector='Technology' AND ipo_year=1998")
self.assertEqual(
{'query': {'bool': {'filter': [
{'term': {'exchange': 'nyse'}},
{'term': {'sector': 'Technology'}},
{'term': {'ipo_year': 1998}},
]}}},
executor.request)
def test_group_by_function(self):
executor = es_query.create_executor(
"SELECT shares_count, COUNT(*) FROM symbol GROUP BY floor(market_cap / last_sale) AS shares_count")
self.assertEqual(
{'aggs': {'shares_count': {
'terms': {'size': 0, 'script': {
'lang': 'expression',
'inline': "floor(doc['market_cap'].value / doc['last_sale'].value)"}},
'aggs': {}}}, 'size': 0},
executor.request)
def test_timestamp(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE ts > TIMESTAMP '2016-08-08 00:00:00'")
self.assertEqual({'query': {
'range': {
'ts': {
'gt': 1470585600000L
}
}
}}, executor.request)
def test_field_lte_numeric(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE last_sale <= 1000")
self.assertEqual({'query': {
'range': {
'last_sale': {
'lte': 1000.0
}
}
}}, executor.request)
def test_and_or_used_parentheses(self):
executor = es_query.create_executor(
"SELECT * FROM symbol WHERE exchange='nyse' AND (sector='Technology' OR ipo_year > 1998)")
self.assertEqual(
{'query': {'bool': {'filter': [
{'term': {'exchange': 'nyse'}},
{'bool': {'should': [
{'term': {'sector': 'Technology'}},
{'range': {'ipo_year': {'gt': 1998.0}}}]}}
]}}},
executor.request)
def test_moving_average_with_params(self):
executor = es_query.create_executor([
"SELECT year, MAX(adj_close) AS max_adj_close, MOVING_Avg(max_adj_close, '{\"window\":5}') AS ma FROM quote "
"WHERE symbol='AAPL' GROUP BY date_trunc('year', \"date\") AS year"])
self.assertEqual(
{'query': {'term': {u'symbol': 'AAPL'}}, 'aggs': {
u'year': {'date_histogram': {'field': u'date', 'interval': 'year', 'time_zone': '+08:00'},
'aggs': {u'max_adj_close': {u'max': {'field': u'adj_close'}},
'ma': {
'moving_avg': {'buckets_path': u'max_adj_close', 'window': 5}}}}}, 'size': 0},
executor.request)
def test_drivative(self):
executor = es_query.create_executor([
"SELECT year, MAX(adj_close) AS max_adj_close, DERIVATIVE(max_adj_close) FROM quote "
"WHERE symbol='AAPL' GROUP BY date_trunc('year', \"date\") AS year"])
self.assertEqual(
{'query': {'term': {u'symbol': 'AAPL'}}, 'aggs': {
u'year': {'date_histogram': {'field': u'date', 'interval': 'year', 'time_zone': '+08:00'},
'aggs': {u'max_adj_close': {u'max': {'field': u'adj_close'}},
'DERIVATIVE(max_adj_close)': {'derivative': {'buckets_path': u'max_adj_close'}}}}},
'size': 0},
executor.request)
def test_serial_diff(self):
executor = es_query.create_executor([
"SELECT year, MAX(adj_close) AS max_adj_close, SERIAL_DIFF(max_adj_close, lag=7) AS ma FROM quote "
"WHERE symbol='AAPL' GROUP BY date_trunc('year', \"date\") AS year"])
self.assertEqual(
{'query': {'term': {u'symbol': 'AAPL'}}, 'aggs': {
u'year': {'date_histogram': {'field': u'date', 'interval': 'year', 'time_zone': '+08:00'},
'aggs': {u'max_adj_close': {u'max': {'field': u'adj_close'}},
u'ma': {u'serial_diff': {'buckets_path': u'max_adj_close', u'lag': 7}}}}},
'size': 0},
executor.request)
def test_group_by_numeric_range(self):
executor = es_query.create_executor(
"SELECT ipo_year_range, COUNT(*) FROM symbol "
"GROUP BY CASE "
" WHEN ipo_year_range >= 2000 THEN 'post_2000' "
" WHEN ipo_year_range < 2000 THEN 'pre_2000' END AS ipo_year_range")
self.assertEqual(
{'aggs': {'ipo_year_range': {
'range': {'ranges': [{'from': 2000.0, 'key': 'post_2000'}, {'to': 2000.0, 'key': 'pre_2000'}],
'field': 'ipo_year_range'}, 'aggs': {}}}, 'size': 0},
executor.request)
def test_join_on_one_field(self):
executor = es_query.create_executor(
'SELECT * FROM quote JOIN matched_symbols ON quote.symbol = matched_symbols.symbol', {
'matched_symbols': [
{'symbol': '1'},
{'symbol': '2'}
]
})
self.assertEqual(
{'query': {'bool': {'filter': [{}, {'terms': {u'symbol': ['1', '2']}}]}}},
executor.request)
def test_two_children(self):
executor = es_query.create_executor([
"WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols",
"SELECT ipo_year, MAX(market_cap) AS max_this_year FROM all_symbols GROUP BY ipo_year LIMIT 1",
"SELECT sector, MAX(market_cap) AS max_this_sector FROM all_symbols GROUP BY sector LIMIT 1"])
rows = executor.select_response({
"hits": {
"hits": [],
"total": 6714,
"max_score": 0.0
},
"_shards": {
"successful": 1,
"failed": 0,
"total": 1
},
"took": 2,
"aggregations": {
"sector": {
"buckets": [
{
"max_this_sector": {
"value": 34620000000.0
},
"key": "n/a",
"doc_count": 1373
}
],
"sum_other_doc_count": 5341,
"doc_count_error_upper_bound": 0
},
"max_all_times": {
"value": 522690000000.0
},
"ipo_year": {
"buckets": [
{
"max_this_year": {
"value": 54171930444.0
},
"key": 2014,
"doc_count": 390
}
],
"sum_other_doc_count": 2508,
"doc_count_error_upper_bound": 0
}
},
"timed_out": False
})
self.assertEqual(
[{'max_this_year': 54171930444.0, 'ipo_year': 2014, 'max_all_times': 522690000000.0, '_bucket_path': ['level2']},
{'sector': 'n/a', 'max_all_times': 522690000000.0, 'max_this_sector': 34620000000.0, '_bucket_path': ['level3']}],
rows)
def test_order_by_can_reference_child_buckets(self):
executor = es_query.create_executor(
["WITH SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol \n"
"GROUP BY ipo_year ORDER BY max_in_finance LIMIT 2 AS per_year",
"SELECT MAX(market_cap) AS max_in_finance FROM per_year WHERE sector='Finance'"])
self.assertEqual(
{'aggs': {
u'ipo_year': {'terms': {'field': u'ipo_year', 'order': {u'level2.max_in_finance': 'asc'}, 'size': 2},
'aggs': {'level2': {'filter': {'term': {u'sector': 'Finance'}},
'aggs': {u'max_in_finance': {u'max': {'field': u'market_cap'}}}}}}},
'size': 0},
executor.request)
def test_join_on_one_field(self):
executor = es_query.create_executor([
"WITH SELECT * FROM symbol WHERE sector='Finance' AS finance_symbols",
'SELECT * FROM quote JOIN finance_symbols ON quote.symbol = finance_symbols.symbol'
])
self.assertEqual(
{'query': {'bool': {'filter': [
{}, {'filterjoin': {
u'symbol': {'indices': u'symbol*', 'path': u'symbol',
'query': {'term': {u'sector': 'Finance'}}}}}]
}}},
executor.request)
def test_having_can_reference_child_buckets(self):
executor = es_query.create_executor(
["WITH SELECT ipo_year, COUNT(*) AS ipo_count FROM symbol \n"
"GROUP BY ipo_year HAVING max_in_finance > 200 AS per_year",
"SELECT MAX(market_cap) AS max_in_finance FROM per_year WHERE sector='Finance'"])
self.assertEqual(
{'aggs': {u'ipo_year': {'terms': {'field': u'ipo_year', 'size': 0}, 'aggs': {
'level2': {'filter': {'term': {u'sector': 'Finance'}},
'aggs': {u'max_in_finance': {u'max': {'field': u'market_cap'}}}}, 'having': {
'bucket_selector': {'buckets_path': {u'max_in_finance': u'level2.max_in_finance'},
'script': {'lang': 'expression', 'inline': u' max_in_finance > 200'}}}}}}, 'size': 0},
executor.request)
