yql overview
DESCRIPTION
This overview will cover:- YQL SELECT, INSERT, UPDATE and DELETE query Syntax- Open Data Tables- YQL ExecuteTRANSCRIPT
1
Jonathan LeBlanc – Technology EvangelistYahoo! Developer NetworkTwitter: @jcleblanc
2
3
YQL Query Structure SELECT * FROM Internet
4
YQL Test Console
Available At: http://developer.yahoo.com/yql/console
5
SELECT myColumn, myTitle FROM myTable(0, 50) WHERE myColumn = 'value' AND myTitle = 'title‘
LIMIT 3 OFFSET 10 |
sort(field='myColumn') | reverse()
Running Queries - SELECT Query Structure
6
Modify Data on the InternetCreate Tweets, Add Blog Entries, Update Databases
Current Tables Using I/U/DTwitter Status, bit.ly URL Shortner, Yahoo! Profile Status / Update Stream, Amazon SimpleDB
Running Queries - INSERT / UPDATE / DELETE
7
INSERT INTO bitly.shorten (login, apiKey, longUrl)
VALUES (’ME', 'API_KEY', 'http://yahoo.com')
Running Queries - INSERT Query Structure
8
UPDATE social.profile.status
SET status="Using YQL UPDATE"
WHERE guid=“NJFIDHVPVVISDXZKT7UKED2WHU”
Running Queries - UPDATE Query Structure
9
DELETE FROM twittertable
WHERE tweetid="12345" AND username="twitter_username" AND password="twitter_password
Running Queries - DELETE Query Structure
10
SELECT * FROM flickr.photos.search
WHERE (lat, lon)
IN (SELECT centroid.latitude, centroid.longitude FROM geo.places WHERE text="SFO")
Running Queries – Sub-selects with the IN clause
11
Running Queries – Available Tables
YQL Command: SHOW Tables
Number of Standard Tables: 113
Number of Community Tables: 610
Total Number of Tables: 723
Contribute Tables to:
http://www.github.com/yql/yql-tables/
12
Running Queries – Table Syntax
YQL Command: DESC tablename
13
Running Queries – URL Format
http://query.yahooapis.com/v1/yql?[params]
http://query.yahooapis.com/v1/public/yql?[params]
Params• q=[QUERY]• format=[XML / JSON] • diagnostics=[true / false]• debug=[true / false]• env=[.env file link]• callback=[function name]
14
http://query.yahooapis.com/v1/yql?[params]
http://query.yahooapis.com/v1/public/yql?[params]
Params• q=[QUERY]• format=[XML / JSON / JSONP / JSONP-X]• diagnostics=[true / false]• debug=[true / false]• env=[.env file link]• callback=[function name]
Running Queries – URL Format
15
http://queries.yahooapis.com/v1/public/yql/
[user_alias]/[query_alias]
Create Your User Alias
INSERT INTO yql.queries (alias) VALUES (”jcleblanc")
Insert a new Query Alias
INSERT INTO yql.queries.query (name, query) VALUES ("weather", ”SELECT * FROM weather.forecast WHERE location=90210")
Query Aliasing – Creating Your Alias
16
YQL Open Data Tables Wrap Your Own API or Data Source
17
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta></meta>
<bindings></bindings>
</table>
Open Data Tables – XML File Syntax
18
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta></meta>
<bindings></bindings>
</table>
Open Data Tables – XML File Syntax
19
<?xml version="1.0" encoding="UTF-8"?><table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta> <author>Jonathan LeBlanc</author> <description>My Table</description> <documentationURL>www.site.com</documentationURL>
<sampleQuery>SELECT * FROM {table} </sampleQuery> </meta> <bindings></bindings></table>
Open Data Tables – XML File Syntax
20
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta></meta>
<bindings></bindings>
</table>
Open Data Tables – XML File Syntax
21
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta></meta>
<bindings>
<select itemPath="" produces="XML" >
<urls>
<url env="all”>http://www.site.com/{user}</url>
</urls>
<inputs>
<key id="user" type="xs:string" paramType="path"
required="true" /> </inputs>
</select>
</bindings>
</table>
Open Data Tables – XML File Syntax
22
The USE clause
USE 'http://github.com/yql/yql-tables/raw/master/google/google.buzz.updates.xml'
AS buzz.updates;
SELECT * FROM buzz.updates
WHERE user='nakedtechnologist'
Open Data Tables – Invoking a Table Definition
23
http://datatables.org/alltables.env
USE 'http://www.datatables.org/amazon/amazon.ecs.xml' AS amazon.ecs;
USE 'http://www.datatables.org/bitly/bit.ly.shorten.xml' AS bit.ly.shorten;
USE 'http://www.datatables.org/dopplr/dopplr.auth.xml' AS dopplr.auth;
USE 'http://www.datatables.org/dopplr/dopplr.city.info.xml' AS dopplr.city.info;
Open Data Tables – .env Files
Google Buzz Table
Available At: http://github.com/yql/yql-tables
25
YQL Execute Extending Open Data Tables
26
Augment data- city / state to APIs that just return zip code
Create APIs from web pages- celebrity birthdays scraped from IMDB
Data Transformations- convert the results from xml to Google's kml format
Move business logic of your application to the cloud
YQL Execute – XML File Syntax
27
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta></meta>
<bindings>
<select itemPath="" produces="XML" >
<urls></urls>
<inputs></inputs>
</select>
</bindings>
</table>
YQL Execute – XML File Syntax
28
<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
<meta></meta>
<bindings>
<select itemPath="" produces="XML" >
<urls></urls>
<inputs></inputs>
<execute></execute>
</select>
</bindings>
</table>
YQL Execute – XML File Syntax
Global Disaster Alert and Coordination System Table
Available At: http://github.com/yql/yql-tables
30
Build applications faster
(and that run faster)
Conclusion
This Presentation
http://www.slideshare.net/jcleblanc/yql-overview
YQL Documentation
http://developer.yahoo.com/yql
YQL Console
http://developer.yahoo.com/yql/console
YQL Github Account (Contribute Tables)
http://github.com/yql/yql-tables
32
Thank You Questions?