In Neo a user can create tabular analytics views on top of analytics collections or other analytics views. Tabular analytics views enable a user to attach type and referential integrity constraint metadata to collections. Using the attached metadata it is possible to use Tableau and the Tableau connector to easily visualize data that is available in the analytics service.
To facilitate the adoption of tabular views we should provide sample shadowed collections and tabular views that are based on data the travel-sample dataset. The creation of the shadowed collections and the tabular datasets should happen together with the loading of the travel-sample data if the analytics service is enabled on the cluster loading the sample.
The shadow collections can be created using the following DDL statements
create analytics scope `travel-sample`.inventory;
|
disconnect link `travel-sample`.inventory.Local;
|
alter collection `travel-sample`.inventory.airline enable analytics;
|
alter collection `travel-sample`.inventory.airport enable analytics;
|
alter collection `travel-sample`.inventory.route enable analytics;
|
alter collection `travel-sample`.inventory.landmark enable analytics;
|
alter collection `travel-sample`.inventory.hotel enable analytics;
|
connect link `travel-sample`.inventory.Local;
|
and the sample tabular views can be created using
create or replace analytics view `travel-sample`.inventory.airline_view(
|
airline_iata_code string not unknown,
|
airline_icao_code string,
|
airline_callsign string,
|
airline_name string, country string)
|
default null
|
primary key (airline_iata_code) not enforced
|
as
|
select
|
iata as airline_iata_code,
|
icao as airline_icao_code,
|
callsign as airline_callsign,
|
name as airline_name,
|
country
|
from `travel-sample`.inventory.airline
|
create or replace analytics view `travel-sample`.inventory.airport_view(
|
airport_iata_code string not unknown,
|
airport_icao_code string,
|
airport_name string,
|
city string,
|
country string,
|
tz string,
|
airport_geo_lat double,
|
airport_geo_lon double,
|
airport_geo_alt bigint)
|
default null
|
primary key (airport_iata_code) not enforced
|
as
|
select
|
faa as airport_iata_code,
|
icao as airport_icao_code,
|
airportname as airport_name,
|
city, country, tz,
|
geo.lat as airport_geo_lat,
|
geo.lon as airport_geo_lon,
|
geo.alt as airport_geo_alt
|
from `travel-sample`.inventory.airport
|
create or replace analytics view `travel-sample`.inventory.route_view(
|
route_id bigint not unknown,
|
airline_iata_code string not unknown,
|
source_airport_iata_code string not unknown,
|
dest_airport_iata_code string not unknown,
|
route_stops bigint,
|
route_equipment string,
|
route_distance double)
|
default null
|
primary key (route_id) not enforced
|
foreign key (airline_iata_code) references `travel-sample`.inventory.airline_view not enforced
|
foreign key (source_airport_iata_code) references `travel-sample`.inventory.airport_view not enforced
|
foreign key (dest_airport_iata_code) references `travel-sample`.inventory.airport_view not enforced
|
as
|
select
|
id as route_id,
|
airline as airline_iata_code,
|
sourceairport as source_airport_iata_code,
|
destinationairport as dest_airport_iata_code,
|
stops as route_stops,
|
equipment as route_equipment,
|
distance as route_distance
|
from `travel-sample`.inventory.route
|
create or replace analytics view `travel-sample`.inventory.route_schedule_view(
|
route_id bigint not unknown,
|
sched_day bigint not unknown,
|
sched_time time not unknown,
|
sched_flight string)
|
default null
|
primary key (route_id, sched_day, sched_time) not enforced
|
foreign key (route_id) references `travel-sample`.inventory.route_view not enforced
|
as
|
select
|
r.id as route_id,
|
s.day as sched_day,
|
s.utc as sched_time,
|
s.flight as sched_flight
|
from `travel-sample`.inventory.route r unnest r.schedule s
|
create or replace analytics view `travel-sample`.inventory.landmark_view(
|
landmark_id bigint not unknown,
|
landmark_name string,
|
landmark_address string,
|
landmark_phone string,
|
landmark_url string,
|
landmark_hours string,
|
landmark_price string,
|
landmark_description string,
|
landmark_geo_lat double,
|
landmark_geo_lon double,
|
country string, city string)
|
default null
|
primary key(landmark_id) not enforced
|
as
|
select
|
id as landmark_id,
|
name as landmark_name,
|
address as landmark_address,
|
phone as landmark_phone,
|
url as landmark_url,
|
hours as landmark_hours,
|
prices as landmark_price,
|
content as landmark_description,
|
geo.lat as landmark_geo_lat,
|
geo.lon as landmark_geo_lon,
|
country,
|
city
|
from `travel-sample`.inventory.landmark
|
create or replace analytics view `travel-sample`.inventory.hotel_view(
|
hotel_id bigint not unknown,
|
hotel_name string,
|
hotel_address string,
|
hotel_phone string,
|
hotel_url string,
|
hotel_price string,
|
hotel_description string,
|
hotel_free_breakfast boolean,
|
hotel_free_internet boolean,
|
hotel_free_parking boolean,
|
hotel_geo_lat double,
|
hotel_geo_lon double,
|
country string,
|
city string)
|
default null
|
primary key(hotel_id) not enforced
|
as
|
select
|
id as hotel_id,
|
name as hotel_name,
|
address as hotel_address,
|
phone as hotel_phone,
|
url as hotel_url,
|
price as hotel_price,
|
description as hotel_description,
|
free_breakfast as hotel_free_breakfast,
|
free_internet as hotel_free_internet,
|
free_parking as hotel_free_parking,
|
geo.lat as hotel_geo_lat,
|
geo.lon as hotel_geo_lon,
|
country,
|
city
|
from `travel-sample`.inventory.hotel
|
create or replace analytics view `travel-sample`.inventory.hotel_review_view(
|
hotel_id bigint not unknown,
|
review_author string not unknown,
|
review_timestamp datetime not unknown,
|
review_rating_overall bigint,
|
review_content string)
|
default null
|
datetime "YYYY-MM-DD hh:mm:ss z"
|
primary key (hotel_id, review_author, review_timestamp) not enforced
|
foreign key (hotel_id) references `travel-sample`.inventory.hotel_view not enforced
|
as
|
select
|
h.id as hotel_id,
|
r.author as review_author,
|
r.date as review_timestamp,
|
r.ratings.overall as review_rating_overall,
|
r.content as review_content
|
from `travel-sample`.inventory.hotel h unnest h.reviews r
|
create or replace analytics view `travel-sample`.inventory.hotel_endorsement_view(
|
hotel_id bigint not unknown,
|
endorsement_author string not unknown)
|
default null
|
primary key (hotel_id, endorsement_author) not enforced
|
foreign key (hotel_id) references `travel-sample`.inventory.hotel_view not enforced
|
as
|
select
|
distinct h.id as hotel_id,
|
e as endorsement_author
|
from `travel-sample`.inventory.hotel h unnest h.public_likes e
|
Improvement
Critical
MB-43002 Add the ability to run analytics queries for sample buckets
