Home » Posts tagged 'sql' (Page 2)
Tag Archives: sql
Oracle launches its version of R #rstats
From-
http://www.oracle.com/us/corporate/press/1515738
Integrates R Statistical Programming Language into Oracle Database 11g
News Facts
Comprehensive In-Database Platform for Advanced Analytics
Supporting Quotes
| Oracle Advanced Analytics — an option to Oracle Database 11g Enterprise Edition – extends the database into a comprehensive advanced analytics platform through two major components: Oracle R Enterprise and Oracle Data Mining. With Oracle Advanced Analytics, customers have a comprehensive platform for real-time analytic applications that deliver insight into key business subjects such as churn prediction, product recommendations, and fraud alerting.
Oracle R Enterprise tightly integrates the open source R programming language with the database to further extend the database with Rs library of statistical functionality, and pushes down computations to the database. Oracle R Enterprise dramatically advances the capability for R users, and allows them to use their existing R development skills and tools, and scripts can now also run transparently and scale against data stored in Oracle Database 11g. Oracle Data Mining provides powerful data mining algorithms that run as native SQL functions for in-database model building and model deployment. It can be accessed through the SQL Developer extension Oracle Data Miner to build, evaluate, share and deploy predictive analytics methodologies. At the same time the high-performance Oracle-specific data mining algorithms are accessible from R. |
||
 |
|
BENEFITS |
||
|
| Oracle R Hadoop Connector | Gives R users high performance native access to Hadoop Distributed File System (HDFS) and MapReduce programming framework. |
|---|
Preview- Google Cloud SQL
From -http://code.google.com/apis/sql/
What is Google Cloud SQL?
Google Cloud SQL is web service that allows you to create, configure, and use relational databases with your App Engine applications. It is a fully-managed service that maintains, manages, and administers your databases, allowing you to focus on your applications and services.
By offering the capabilities of a MySQL database, the service enables you to easily move your data, applications, and services into and out of the cloud. This allows for high data portability and helps in faster time-to-market because you can quickly leverage your existing database (using JDBC and/or DB-API) in your App Engine application.
Here is where you can get an invite to the beta only Google Cloud SQL
Sign up for Limited Preview
Google Cloud SQL is available to a limited number of users. To sign up for the service:
- Visit the Google APIs Console. The console opens the All services pane.
- Find the SQL Service line in the Services table and click Request access…
- Fill out the enrollment form.
- Our team will review your enrollment information and respond by email to the address associated with your Google Account.
- Follow the link in the email to view the Terms of Service. Please read these carefully before accepting.
- Sign up for the google-cloud-sql-announce group to receive important announcements and product news. (NOTE- Members: 384)
Google Cloud SQL
Another xing bang API from the boyz in Mountain View. (entry by invite only) But it is free and you can test your stuff on a MySQL db =10 GB
Database as a service ? (Maybe)— while Amazon was building fires (and Fire)
—————————————————————–
https://code.google.com/apis/sql/index.html
What is Google Cloud SQL?
Google Cloud SQL is a web service that provides a highly available, fully-managed, hosted SQL storage solution for your App Engine applications.
What are the benefits of using Google Cloud SQL?
You can access a familiar, highly available SQL database from your App Engine applications, without having to worry about provisioning, management, and integration with other Google services.
How much does Google Cloud SQL cost?
We will not be billing for this service in 2011. We will give you at least 30 days’ advance notice before we begin billing in the future. Other services such as Google App Engine, Google Cloud Storage etc. that you use with Google Cloud SQL may have their own payment terms, and you need to pay for them. Please consult their documentation for details.
Currently you are limited to the three instance sizes. What if I need to store more data or need better performance?
In the Limited Preview period, we only have three sizes available. If you have specific needs, we would like to hear from you on our google-cloud-sqldiscussion board.
When is Google Cloud SQL be out of Limited Preview?
We are working hard to make the service generally available.We don’t have a firm date that we can announce right now.
Do you support all the features of MySQL?
In general, Google Cloud SQL supports all the features of MySQL. The following are lists of all the unsupported features and notable differences that Google Cloud SQL has from MySQL.
Unsupported Features:
- User defined functions
- MySql replication
Unsupported MySQL statements:
LOAD DATA INFILESELECT ... INTO OUTFILESELECT ... INTO DUMPFILEINSTALL PLUGIN .. SONAME ...UNINSTALL PLUGINCREATE FUNCTION ... SONAME ...
Unsupported SQL Functions:
LOAD_FILE()
Notable Differences:
-
- If you want to import databases with binary data into your Google Cloud SQL instance, you must use the
--hex-bloboption withmysqldump.Although this is not a required flag when you are using a local MySQL server instance and the MySQL command line, it is required if you want to import any databases with binary data into your Google Cloud SQL instance. For more information, see Importing Data.
- If you want to import databases with binary data into your Google Cloud SQL instance, you must use the
- How large a database can I use with Google Cloud SQL?
- Currently, in this limited preview period, your database instance must be no larger than 10GB.
- How can I be notified when there are any changes to Google Cloud SQL?
- You can sign up for the sql-announcements forum where we post announcements and news about the Google Cloud SQL.
- How can I cancel my Google Cloud SQL account?
- To remove all data from your Google Cloud SQL account and disable the service:
- Delete all your data. You can remove your tables, databases, and indexes using the
dropcommand. For more information, see SQL DROP statement. - Deactivate the Google Cloud SQL by visiting the Services pane and clicking the On button next to Google Cloud SQL. The button changes from Onto Off.
- Delete all your data. You can remove your tables, databases, and indexes using the
- How do I report a bug, request a feature, or ask a question?
- You can report bugs and request a feature on our project page.You can ask a question in our discussion forum.
Getting Started
- Can I use languages other than Java or Python?
- Only Java and Python are supported for Google Cloud SQL.
- Can I use Google Cloud SQL outside of Google App Engine?
- The Limited Preview is primarily focused on giving Google App Engine customers the ability to use a familiar relational database environment. Currently, you cannot access Google Cloud SQL from outside Google App Engine.
- What database engine are we using in the Google Cloud SQL?
- MySql Version 5.1.59
- Do I need to install a local version of MySQL to use the Development Server?
- Yes.
Managing Your Instances
- Do I need to use the Google APIs Console to use Google Cloud SQL?
- Yes. For basic tasks like granting access control to applications, creating instances, and deleting instances, you need to use the Google APIs Console.
- Can I import or export specific databases?
- No, currently it is not possible to export specific databases. You can only export your entire instance.
- Do I need a Google Cloud Storage account to import or export my instances?
- Yes, you need to sign up for a Google Cloud Storage account or have access to a Google Cloud Storage account to import or export your instances. For more information, see Importing and Exporting Data.
- If I delete my instance, can I reuse the instance name?
- Yes, but not right away. The instance name is reserved for up to two months before it can be reused.
Tools & Resources
- Can I use Django with Google Cloud SQL?
- No, currently Google Cloud SQL is not compatible with Django.
- What is the best tool to use for interacting with my instance?
- There are a variety of tools available for Google Cloud SQL. For executing simple statements, you can use the SQL prompt. For executing more complicated tasks, you might want to use the command line tool. If you want to use a tool with a graphical interface, the SQuirrel SQL Client provides an interface you can use to interact with your instance.
Common Technical Questions
- Should I use InnoDB for my tables?
- Yes. InnoDB is the default storage engine in MySQL 5.5 and is also the recommended storage engine for Google Cloud SQL. If you do not need any features that require MyISAM, you should use InnoDB. You can convert your existing tables using the following SQL command, replacing
tablenamewith the name of the table to convert:ALTER tablename ENGINE = InnoDB;
If you have a
mysqldumpfile where all your tables are in MyISAM format, you can convert them by piping the file through a sed script:mysqldump --databases database_name [-u username -p password] --hex-blob database_name | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > database_file.sql
Warning: You should not do this if your
mysqldumpfile contains the mysql schema. Those files must remain in MyISAM. - Are there any size or QPS limits?
- Yes, the following limits apply to Google Cloud SQL:
Resource Limits from External Requests Limits from Google App Engine Queries Per Second (QPS) 5 QPS No limit Maximum Request Size 16 MB Maximum Response Size 16 MB Google App Engine Limits
Google App Engine applications are also subject to additional Google App Engine quotas and limits. Requests from Google App Engine applications to Google Cloud SQL are subject to the following time limits:
- All database requests must finish within the HTTP request timer, around 60 seconds.
- Offline requests like cron tasks have a time limit of 10 minutes.
- Backend requests to Google Cloud SQL have a time limit of 10 minutes.
App Engine-specific quotas and access limits are discussed on the Google App Engine Quotas page.
- Should I use Google Cloud SQL with my non-High Replication App Engine application?
- We recommend that you use Google Cloud SQL with High Replication App Engine applications. While you can use use Google Cloud SQL with applications that do not use high replication, doing so might impact performance.
- Source-
- https://code.google.com/apis/sql/faq.html#supportmysqlfeatures
Knowledge Discovery in Databases -KDD using PostgreSQL and #Rstats
Here is a small brief primer for beginners on configuring an open source database and using an open source analytics package.
All you need to know – is to read!
http://www.postgresql.org/download/windowsInstall PostgreSQL
Remember to store /memorize the password for the user postgres!
Create a connection using pgAdmin feature in Start Menu
2. download ODBC driver from
http://www.postgresql.org/ftp/odbc/versions/msi/
and the Win 64 edition from
http://wwwmaster.postgresql.org/download/mirrors-ftp/odbc/versions/msi/psqlodbc_09_00_0310-x64.zip
install ODBC driver
3. Go to
Start Menu\Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)
4. Configure the following details in System DSN and User DSN using the ADD tabs .Test connection to check if connection is working
5. Start R and install and load library RODBC
6. Use following initial code for R- if you know SQL you can do the rest
> library(RODBC)
> odbcDataSources(type = c(“all”, “user”, “system”))
SQLServer PostgreSQL30 PostgreSQL35W
“SQL Server” ”PostgreSQL ANSI(x64)” “PostgreSQL Unicode(x64)”
> ajay=odbcConnect(“PostgreSQL30″, uid = “postgres”, pwd = “XX”)
> sqlTables(ajay)
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
1 postgres public names TABLE
> crimedat <- sqlFetch(ajay, “names”)
Using Google Fusion Tables from #rstats
But after all that- I was quite happy to see Google Fusion Tables within Google Docs. Databases as a service ? Not quite but still quite good, and lets see how it goes.
https://www.google.com/fusiontables/DataSource?dsrcid=implicit&hl=en_US&pli=1
http://googlesystem.blogspot.com/2011/09/fusion-tables-new-google-docs-app.html
But what interests me more is
http://code.google.com/apis/fusiontables/docs/developers_guide.html
The Google Fusion Tables API is a set of statements that you can use to search for and retrieve Google Fusion Tables data, insert new data, update existing data, and delete data. The API statements are sent to the Google Fusion Tables server using HTTP GET requests (for queries) and POST requests (for inserts, updates, and deletes) from a Web client application. The API is language agnostic: you can write your program in any language you prefer, as long as it provides some way to embed the API calls in HTTP requests.
The Google Fusion Tables API does not provide the mechanism for submitting the GET and POST requests. Typically, you will use an existing code library that provides such functionality; for example, the code libraries that have been developed for the Google GData API. You can also write your own code to implement GET and POST requests.
Also see http://code.google.com/apis/fusiontables/docs/sample_code.html
Google Fusion Tables API Sample Code
Libraries
SQL API
| Language | Library | Public repository | Samples |
|---|---|---|---|
| Python | Fusion Tables Python Client Library | fusion-tables-client-python/ | Samples |
| PHP | Fusion Tables PHP Client Library | fusion-tables-client-php/ | Samples |
Featured Samples
An easy way to learn how to use an API can be to look at sample code. The table above provides links to some basic samples for each of the languages shown. This section highlights particularly interesting samples for the Fusion Tables API.
SQL API
| Language | Featured samples | API version |
|---|---|---|
| cURL |
|
SQL API |
| Google Apps Script |
|
SQL API |
| Java |
|
SQL API |
| Python |
|
Docs List API |
| Android (Java) |
|
SQL API |
| JavaScript – FusionTablesLayer | Using the FusionTablesLayer, you can display data on a Google Map
Also check out FusionTablesLayer Builder, which generates all the code necessary to include a Google Map with a Fusion Table Layer on your own website. |
FusionTablesLayer, Google Maps API |
| JavaScript – Google Chart Tools | Using the Google Chart Tools, you can request data from Fusion Tables to use in visualizations or to display directly in an HTML page. Note: responses are limited to 500 rows of data. | Google Chart Tools |
External Resources
Google Fusion Tables is dedicated to providing code examples that illustrate typical uses, best practices, and really cool tricks. If you do something with the Google Fusion Tables API that you think would be interesting to others, please contact us at googletables-feedback@google.com about adding your code to our Examples page.
- Shape EscapeA tool for uploading shape files to Fusion Tables.
- GDALOGR Simple Feature Library has incorporated Fusion Tables as a supported format.
- Arc2CloudArc2Earth has included support for upload to Fusion Tables via Arc2Cloud.
- Java and Google App EngineODK Aggregate is an AppEngine application by the Open Data Kit team, uses Google Fusion Tables to store survey data that is collected through input forms on Android mobile phones. Notable code:
- Create a Fusion Table - FusionTableServlet.java
- Insert data into a Fusion Table - SubmissionFusionTable.java
- R packageAndrei Lopatenko has written an R interface to Fusion Tables so Fusion Tables can be used as the data store for R.
- RubySimon Tokumine has written a Ruby gem for access to Fusion Tables from Ruby.
Updated-You can use Google Fusion Tables from within R from http://andrei.lopatenko.com/rstat/fusion-tables.R
ft.connect <- function(username, password) {
url = "https://www.google.com/accounts/ClientLogin";
params = list(Email = username, Passwd = password, accountType="GOOGLE", service= "fusiontables", source = "R_client_API")
connection = postForm(uri = url, .params = params)
if (length(grep("error", connection, ignore.case = TRUE))) {
stop("The wrong username or password")
return ("")
}
authn = strsplit(connection, "\nAuth=")[[c(1,2)]]
auth = strsplit(authn, "\n")[[c(1,1)]]
return (auth)
}
ft.disconnect <- function(connection) {
}
ft.executestatement <- function(auth, statement) {
url = "http://tables.googlelabs.com/api/query"
params = list( sql = statement)
connection.string = paste("GoogleLogin auth=", auth, sep="")
opts = list( httpheader = c("Authorization" = connection.string))
result = postForm(uri = url, .params = params, .opts = opts)
if (length(grep("<HTML>\n<HEAD>\n<TITLE>Parse error", result, ignore.case = TRUE))) {
stop(paste("incorrect sql statement:", statement))
}
return (result)
}
ft.showtables <- function(auth) {
url = "http://tables.googlelabs.com/api/query"
params = list( sql = "SHOW TABLES")
connection.string = paste("GoogleLogin auth=", auth, sep="")
opts = list( httpheader = c("Authorization" = connection.string))
result = getForm(uri = url, .params = params, .opts = opts)
tables = strsplit(result, "\n")
tableid = c()
tablename = c()
for (i in 2:length(tables[[1]])) {
str = tables[[c(1,i)]]
tnames = strsplit(str,",")
tableid[i-1] = tnames[[c(1,1)]]
tablename[i-1] = tnames[[c(1,2)]]
}
tables = data.frame( ids = tableid, names = tablename)
return (tables)
}
ft.describetablebyid <- function(auth, tid) {
url = "http://tables.googlelabs.com/api/query"
params = list( sql = paste("DESCRIBE", tid))
connection.string = paste("GoogleLogin auth=", auth, sep="")
opts = list( httpheader = c("Authorization" = connection.string))
result = getForm(uri = url, .params = params, .opts = opts)
columns = strsplit(result,"\n")
colid = c()
colname = c()
coltype = c()
for (i in 2:length(columns[[1]])) {
str = columns[[c(1,i)]]
cnames = strsplit(str,",")
colid[i-1] = cnames[[c(1,1)]]
colname[i-1] = cnames[[c(1,2)]]
coltype[i-1] = cnames[[c(1,3)]]
}
cols = data.frame(ids = colid, names = colname, types = coltype)
return (cols)
}
ft.describetable <- function (auth, table_name) {
table_id = ft.idfromtablename(auth, table_name)
result = ft.describetablebyid(auth, table_id)
return (result)
}
ft.idfromtablename <- function(auth, table_name) {
tables = ft.showtables(auth)
tableid = tables$ids[tables$names == table_name]
return (tableid)
}
ft.importdata <- function(auth, table_name) {
tableid = ft.idfromtablename(auth, table_name)
columns = ft.describetablebyid(auth, tableid)
column_spec = ""
for (i in 1:length(columns)) {
column_spec = paste(column_spec, columns[i, 2])
if (i < length(columns)) {
column_spec = paste(column_spec, ",", sep="")
}
}
mdata = matrix(columns$names,
nrow = 1, ncol = length(columns),
dimnames(list(c("dummy"), columns$names)), byrow=TRUE)
select = paste("SELECT", column_spec)
select = paste(select, "FROM")
select = paste(select, tableid)
result = ft.executestatement(auth, select)
numcols = length(columns)
rows = strsplit(result, "\n")
for (i in 3:length(rows[[1]])) {
row = strsplit(rows[[c(1,i)]], ",")
mdata = rbind(mdata, row[[1]])
}
output.frame = data.frame(mdata[2:length(mdata[,1]), 1])
for (i in 2:ncol(mdata)) {
output.frame = cbind(output.frame, mdata[2:length(mdata[,i]),i])
}
colnames(output.frame) = columns$names
return (output.frame)
}
quote_value <- function(value, to_quote = FALSE, quote = "'") {
ret_value = ""
if (to_quote) {
ret_value = paste(quote, paste(value, quote, sep=""), sep="")
} else {
ret_value = value
}
return (ret_value)
}
converttostring <- function(arr, separator = ", ", column_types) {
con_string = ""
for (i in 1:(length(arr) - 1)) {
value = quote_value(arr[i], column_types[i] != "number")
con_string = paste(con_string, value)
con_string = paste(con_string, separator, sep="")
}
if (length(arr) >= 1) {
value = quote_value(arr[length(arr)], column_types[length(arr)] != "NUMBER")
con_string = paste(con_string, value)
}
}
ft.exportdata <- function(auth, input_frame, table_name, create_table) {
if (create_table) {
create.table = "CREATE TABLE "
create.table = paste(create.table, table_name)
create.table = paste(create.table, "(")
cnames = colnames(input_frame)
for (columnname in cnames) {
create.table = paste(create.table, columnname)
create.table = paste(create.table, ":string", sep="")
if (columnname != cnames[length(cnames)]){
create.table = paste(create.table, ",", sep="")
}
}
create.table = paste(create.table, ")")
result = ft.executestatement(auth, create.table)
}
if (length(input_frame[,1]) > 0) {
tableid = ft.idfromtablename(auth, table_name)
columns = ft.describetablebyid(auth, tableid)
column_spec = ""
for (i in 1:length(columns$names)) {
column_spec = paste(column_spec, columns[i, 2])
if (i < length(columns$names)) {
column_spec = paste(column_spec, ",", sep="")
}
}
insert_prefix = "INSERT INTO "
insert_prefix = paste(insert_prefix, tableid)
insert_prefix = paste(insert_prefix, "(")
insert_prefix = paste(insert_prefix, column_spec)
insert_prefix = paste(insert_prefix, ") values (")
insert_suffix = ");"
insert_sql_big = ""
for (i in 1:length(input_frame[,1])) {
data = unlist(input_frame[i,])
values = converttostring(data, column_types = columns$types)
insert_sql = paste(insert_prefix, values)
insert_sql = paste(insert_sql, insert_suffix) ;
insert_sql_big = paste(insert_sql_big, insert_sql)
if (i %% 500 == 0) {
ft.executestatement(auth, insert_sql_big)
insert_sql_big = ""
}
}
ft.executestatement(auth, insert_sql_big)
}
}
Interview Eberhard Miethke and Dr. Mamdouh Refaat, Angoss Software
Here is an interview with Eberhard Miethke and Dr. Mamdouh Refaat, of Angoss Software. Angoss is a global leader in delivering business intelligence software and predictive analytics solutions that help businesses capitalize on their data by uncovering new opportunities to increase sales and profitability and to reduce risk. (more…)
Cloud Computing by Windows , Amazon and Google for free
Some ways to test and use cloud computing for free for yourself-
- Windows Azure
- Amazon Ec2
- Google Storage
The folks at Microsoft Azure announced a 90 day free trial (more…)
