2017년 7월 15일 토요일

Fusion Tables REST API


This document explains how to get started using the Google Fusion Tables API v2.0.

Contents
Introduction
Google Fusion Tables is a web application used for sharing, visualizing, and publishing tabular data. You can upload your own CSV, KML, ODS, XLS, or Google Spreadsheet data to a Fusion Tables table. Once your data is in Fusion Tables, you can collaborate on it with others in real time, publish it for Google Search, create map and chart visualizations for private use or for embedding on websites, filter it according to specific criteria, and update the data behind your visualizations or filters at any time.
The Fusion Tables API allows you to use HTTP requests to programmatically to perform these tasks, which are also available in the Fusion Tables web application:

⦁ create and delete tables
⦁ read and modify table metadata such as table and column names and column types
⦁ insert, update, and delete the rows in a table
⦁ create, update, and delete settings for certain visualizations
⦁ query the rows in a table

For example, you can use the Fusion Tables API to apply the same map style and info window appearance to several tables, update the table's data and therefore update embedded visualizations, add and populate a new column for a table, read a public table's metadata, or query a table's contents.

Table structure, metadata, and visualization settings are represented as JSON data structures accessible through RESTful HTTP requests. Row data is handled using a subset of SQL statements sent as HTTP requests, and can be retrieved in either CSV or JSON formats.
Fusion Tables API documentation includes:

⦁ This document, Getting Started, orients developers to the Fusion Tables API: what it is and how it works.
⦁ A guide for developers, called "Using the API," provides step-by-step instructions on how to use the API's common features.
⦁ A Reference document provides detailed information about the data structures, method calls, and authentication scopes of the API.
⦁ A Row and Query SQL Reference guide that provides details for the SELECT, INSERT, UPDATE, and DELETE statements used to manage rows and query the table's content.

Note: You may find it helpful to look at some of our sample code and try the available client libraries for the Google Fusion Tables API v2.0.
About REST
REST is a style of software architecture that provides a convenient and consistent approach to requesting and modifying data.
The term REST is short for "Representational State Transfer." In the context of Google APIs, it refers to using HTTP verbs to retrieve and modify representations of data stored by Google.
In a RESTful system, resources are stored in a data store; a client sends a request that the server perform a particular action (such as creating, retrieving, updating, or deleting a resource), and the server performs the action and sends a response, often in the form of a representation of the specified resource.
In Google's RESTful APIs, the client specifies an action using an HTTP verb such as POSTGETPUT, or DELETE. It specifies a resource by a globally-unique URI of the following form:

https://www.googleapis.com/apiName/apiVersion/resourcePath?parameters
Because all API resources have unique HTTP-accessible URIs, REST enables data caching and is optimized to work with the web's distributed infrastructure.
For more information about REST, you may find the following third-party documents useful:
⦁ Building Web Services the REST Way, which is aimed at service providers but provides a good overview of REST
⦁ HTTP 1.1 method definitions; specification for GET, POST, PUT, and DELETE
Before you start
If you're unfamiliar with Google Fusion Tables, read this document and try the basic tutorials before starting to code. This document assumes that you're familiar with web programming concepts and web data formats.
Get a Google account
Make sure that you have a Google account set up. We recommend that you use a separate Google account for development and testing purposes to protect yourself from accidental data loss. If you already have a test account, then you're all set; you can visit the Google Fusion Tables user interface to set up, edit, or view your test data. If you don't, go ahead and create one now for Fusion Tables use.
Learn about authorizing requests and identifying your application
When your application requests private data or resources, the request must be authorized by an authenticated user who has access to that data. When your application requests data or resources from a public or unlisted table, the request doesn't need to be authorized, but it does need to be accompanied by an API key.
For information about how to authorize requests and use API keys, see Authorizing requests and identifying your application.
Fusion Tables API overview
The Google Fusion Tables API v2.0 is a RESTful API that allows developers to create and manage Fusion Tables resources such as tables, columns, rows, templates, and styles. Developers use SQL statements to query and manipulate rows and they use the RESTful API to work with tables, columns, styles, and templates.
Basic concepts
Google Fusion Tables is built on the following concepts:

⦁ Table: Tables consist of data arranged in rows and columns, similar to a spreadsheet. There are three types of tables: base, view, and merged.
  ⚬ base: A table created by uploading data from a repository or through inserting rows with SQL SELECT statements. This is the table from which you create views.
  ⚬ view: A table created by selecting a subset of columns from a base table with or without filters on the row data. A view is a virtual table, with its own metadata, providing a window into the underlying base table. A view may have a filter applied so that it includes only a subset of rows and columns. Currently, you can only programmatically create a view with the SQL API, not with Fusion Tables API.
  ⚬ merged: A table created by combining two or more tables, each of which must have a column containing values that match a key column in the other table(s). Merged tables inherit the column types, data, and edit permissions on data from the base table. You can merge tables owned by different people, but you cannot insert new rows into a merged table.

⦁ Table ID: The table identifier is an encrypted string value such as1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc. 
  ⚬ There are several ways to get a table's ID:
  ⚬ Choose the File > About menu command in the web application.
  ⚬ Use the API v2.0 to retrieve a list of all tables where the authenticated user is the owner or editor.
  ⚬ Use the Google Docs List API with the table's name (See thecode sample and the Google Docs List API documentation for details).

⦁ Column: A column within a table. Each column has an ID, a name, and an assigned data type: "STRING", "NUMBER", "DATETIME", or "LOCATION". The column ID is a string value. See Working with columns for details on how the ID is parsed. 

⦁ Template: A layout template that defines the content and appearance of the row data that is displayed when a user clicks on a marker, line, or polygon in a map visualization. According to theGoogle Maps API documentation, which calls this information aninfo window, "The info window looks a little like a comic-book word balloon; it has a content area and a tapered stem, where the tip of the stem is at a specified location on the map." You can customizethe template by selecting which columns to display and by specifying a custom layout using HTML code.

⦁ Style: A style definition for map features. Features displayed on a map can be assigned a single style, styled per a column containing style definitions, or styled per a column containing numerical data. You can configure the styles in a map. 

⦁ Row: Fusion Table data is stored in rows, each of which has an internally generated ROWID that identifies it uniquely within a table. To locate a specific row of data, issue an SQL query statement with criteria to match that row. The response includes the row's ROWID, which you can now use to update or delete that row.
Basic operations
For Table, Column, Template, and Style Resources, you can use HTTP requests as described in the following table.
OperationDescriptionHTTP mappings
listLists all resources of this type.GET on a resource list URI.
getGets a specific resource.GET on a resource URI.
insertInserts a new resource (create a new resource).POST on a resource list URI, where you pass in data for a new resource.
updateUpdates a specific resource.PUT on a resource URI, where you pass in data for the updated resource.
deleteDeletes a specific resource.DELETE on a resource URI.

For rows, you can use SQL statements with HTTP requests, as described in the following table. SQL queries are sent to the following URL endpoint as the value of an "sql" parameter:https://www.googleapis.com/fusiontables/v2/query?sql=. See Sending requests for examples.
OperationDescriptionQuery format of the "sql" parameter
listLists all rows within a table.GET with a specific table ID: 
  SELECT ROWID FROM <table_id>
getGets a specific row.GET with a specific table ID and criteria:
  SELECT ROWID FROM <table_id> WHERE <your filter>
insertInserts a new row into a table.POST with a specific ROWID, where you pass in data for a new row: 
  INSERT INTO <table_id> (<column_name> {, <column_name>}*) 
    VALUES (<value> {, <value>}*)
updateUpdates a specific row.POST with a specific ROWID, where you pass in data for the updated row: 
  UPDATE <table_id> SET <column_name> = <value> {, <column_name> = <value> }* 
    WHERE ROWID = <row_id>
deleteDeletes a specific row.POST with a specific ROWID: 
  DELETE FROM <table_id> {WHERE ROWID = <row_id>}
You can also use SQL statements to create views and merged tables.
Authentication requirements
The authentication requirements for owners and editors of a table are summarized in the table below. Viewers can only perform simple GEToperations on individual resources. See Table access permissions.
Resource
Authentication Required?
List resourcesGet resourceInsertUpdateDelete
TablesyesPrivate tables onlyyesyesyes
ColumnsPrivate tables onlyPrivate tables onlyyesyesyes
TemplatesPrivate tables onlyPrivate tables onlyyesyesyes
StylesPrivate tables onlyPrivate tables onlyyesyesyes
RowsPrivate tables onlyPrivate tables onlyyesyesyes

Sending requests
The Google Fusion Tables API v2.0 supports these approaches to sending requests to the Fusion Tables server:

⦁ Using REST for tables, columns, styles, and templates
⦁ Using SQL for rows

Regardless of which you use, the URI is:

https://www.googleapis.com/fusiontables/v2
Sending directly from the browser
If a table is exportable and either public or unlisted, you can send a GETrequest directly from the URL bar of your browser, but you need to include an API key. Here are a couple examples for you to try.
This example lists all columns in a given table (try this in your browser):

https://www.googleapis.com/fusiontables/v2/tables/1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4/columns?key=your-api-keykey
This example runs a query to select all rows in a given table (try this in your browser):

https://www.googleapis.com/fusiontables/v2/query?sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4&
Invoking the API for tables, columns, styles, and templates
The supported API v2.0 operations map directly to REST HTTP verbs, as described in API v2.0 operations.
The specific format for API v2.0 URIs are:

https://www.googleapis.com/fusiontables/v2/resourceID?parameters
where resourceID is the identifier for a resource, and parameters are any parameters to apply to the query. See RESTful URL query parameters.
The format of the resourceID path extensions lets you identify the resource or resource list you're currently operating on, for example:

https://www.googleapis.com/fusiontables/v2/tables https://www.googleapis.com/fusiontables/v2/tables/tableId https://www.googleapis.com/fusiontables/v2/tables/tableId/columns https://www.googleapis.com/fusiontables/v2/tables/tableId/columns/columnId https://www.googleapis.com/fusiontables/v2/tables/tableId/templates https://www.googleapis.com/fusiontables/v2/tables/tableId/templates/templateId https://www.googleapis.com/fusiontables/v2/tables/tableId/styles https://www.googleapis.com/fusiontables/v2/tables/tableId/styles/styleId
The full set of supported URIs is summarized in the Fusion Tables API Reference document. Here are some examples.
List all templates for table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

GET https://www.googleapis.com/fusiontables/v2/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates
Get template 1:

GET https://www.googleapis.com/fusiontables/v2/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates/1
Invoking the API for rows
The specific format for API v2.0 URIs used with SQL statements when handling rows is:

https://www.googleapis.com/fusiontables/v2/query
The format for a SQL statement is:

https://www.googleapis.com/fusiontables/v2/query?sql=query
where query is a valid SQL query. Here are a couple of examples of how this works:
To list all the ROWIDs for table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc, send an authenticated GET request to this URL with this SQL statement:

https://www.googleapis.com/fusiontables/v2/query?sql=SELECT ROWID FROM 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc
To delete row 201, send an authenticated POST request to this URL with this SQL statement:

https://www.googleapis.com/fusiontables/v2/query?sql=DELETE FROM 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc WHERE ROWID = 201
Calling the API from a browser using JavaScript
You can invoke the API v2.0 using REST or SQL from JavaScript on public or unlisted tables using the callback query parameter and a callback function. This allows you to write rich applications that display Google Fusion Tables data without writing any server side code.
The following example uses this approach to display all templates for table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

<html>   <head>     <title>Google Fusion Tables API Example</title>   </head>   <body>     <div id="content"></div>     <script>     function handler(response) {       for (var i = 0; i < response.items.length; i++) {         var item = response.items[i];         // Either show the body or the automatic columns of the template         if (item.body) {           document.getElementById("content").innerHTML += "<br>" + item.body;         } else {           for (var j = 0; j < item.automaticColumnNames.length; j++) {             document.getElementById("content").innerHTML += "<br>" + item.automaticColumnNames[j];           }         }       }     }     </script>     <script src="https://www.googleapis.com/fusiontables/v2/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates?callback=handler&key=your-api-key"></script>   </body> </html>
Data formats
The Fusion Tables API allows developers to work with specific data type formats. You can define the format for response data that is returned from a RESTful or SQL call. Google Fusion Tables handles different data types in predefined ways, so you can reliably manipulate the data that is returned.
Specifying the response data format
The default format for API v2.0 response data for a successful API call is JSON typed data. If you are using SQL to query rows, you can choose to receive untyped CSV or JSON (typed or untyped) data.
JSON (JavaScript Object Notation) is a common, language-independent data format that provides a simple text representation of arbitrary data structures. For more information, see json.org.
API ResourceDefault formatAlternative format
RowsTyped JSONUse the alt=csv parameter to receive data in untyped CSV format. 
Use the typed=false parameter to receive untyped JSON data.
Tables, Columns, Styles, TemplatesTyped JSONNone
Parsing different data types
If response data is returned as JSON, then all numbers are Typed JSON values by default. You can change the default by using "typed=false" parameter. If you do use "typed=false", or the return format is not JSON, then Fusion Tables returns the original string value.
This table summarizes how numbers and location values are returned from the Google Fusion Tables API v2.0 when typed.
Column data typeReturned asExamples
Integer numbersIntegersInput as: 0, 1, 3435266
Returned as: 0, 1, 3435266
Other numbersNumeric (double) valuesInput as: 0.0, 1.0, 3435266.0, 12.0, 0.5 
Returned as: 0.0, 1.0, 3435266.0, 12.0, 0.5
Extremely large (> 1e16) or small (< 1e-6) numbersScientific notationInput as: 0.000000123, 12345678901234560 
Returned as: 1.23e-7, 1.234567890123456E16
Unparsable numbersNaNInput as: anything except a number
Returned as: NaN
Individual latitude and longitude valuesNumeric (double) valuesInput as: 37, -122
Returned as: 37.0, or -122.0
Location coordinate pairs(A lat-long pair combined in a single location column)Returned as a UTF-8 string. Parsed out as two numeric (double) values and sorted as a string of the two numbers concatenated.Input as: -73.12345678,40.12345678
Returned as: -73.12345678,40.12345678
Latitude or longitude in the master column of a two-column locationNumeric (double) valuesInput as: 37.42114440, or -122.08530320
Returned as: 37.42114440, or -122.08530320
KMLGeoJSON

{ "geometry": { "type": "Point", "coordinates": [ -73.12345678, 40.12345678 ] } }

댓글 없음:

댓글 쓰기