Upload and select query performance on HIVE and BigQuery:

HIVE performance time capturing:
BigQuery performance time capturing:
Normal table load (7009729 records)- Time taken: 33.432 seconds
select count(1) from pocbigquery.github_nested_copy; - 1.6s
Partitioned table load (7009729 records) - Time taken: 31.152 seconds
select count(1) from pocbigquery.github_nested_copy where repository.has_issues=FALSE;Query complete (2.0s elapsed, 2.18 MB processed)
External table load(7009729 records) - Time taken: 32.022 seconds
SELECT * FROM pocbigquery.github_nested_copy WHERE SUBSTR(repository.created_at,1,4)='2010';Query complete (21.5s elapsed, 1.58 GB processed)
Views load(7009729 records) - Time taken: 0.38 seconds
SELECT * FROM pocbigquery.github_nested_copy WHERE SUBSTR(repository.created_at,1,4)='2009' AND repository.has_issues=FALSE;Query complete (8.2s elapsed, 1.58 GB processed)
Index - Index on VIRTUAL VIEW is not supported.

where clause query on normal table ‘select uniquecarrier from flightdata where uniquecarrier='WN' limit 1000;’
Time taken: 1.695 seconds, Fetched: 1000 row(s)
SELECT * FROM pocbigquery.githubtest WHERE SUBSTR(repository.created_at,1,4)='2010';Query complete (22.9s elapsed, 1.58 GB processed)
Managed partitioned table ‘select uniquecarrier from flightdatapartone where monthpart='WN' limit 50;’ Time taken: 0.274 seconds, Fetched: 50 row(s)   
SELECT * FROM pocbigquery.githubtest where cast (parse_timestamp('%Y/%m/%d %T %z',repository.created_at) as date)='2010-03-07' limit 10;Query complete (5.2s elapsed, 1.58 GB processed)
External table ‘select uniquecarrier from flightDataExt where uniquecarrier='WN' limit 50;’
Time taken: 3.696 seconds, Fetched: 50 row(s)
SELECT * FROM pocbigquery.github_nested_copy where cast (parse_timestamp('%Y/%m/%d %T %z',repository.created_at) as date)='2010-03-07';Query complete (9.5s elapsed, 1.58 GB processed)
‘Select uniquecarrier from flightDataPart where uniquePart='WN' limit 50;’ Time taken: 0.344 seconds, Fetched: 50 row(s)

SELECT * FROM pocbigquery.githubtest where cast (parse_timestamp('%Y/%m/%d %T %z',repository.created_at) as date)='2010-03-07';Query complete (10.6s elapsed, 1.58 GB processed)

SELECT * FROM pocbigquery.githubtest where _PARTITIONTIME=TIMESTAMP('2017-09-18');Query complete (60.9s elapsed, 1.58 GB processed)