Menu
  • HOME
  • TAGS

how to 'flatten' the table in dashDB created by the schema discovery process (SDP)?

Tag: cloudant,dashdb,cloudant-sdp

I've used the Cloudant schema discovery process (SDP) to create and populate a table in dashDB. The data in Cloudant is time series in nature:

...
{ "date": "20150101T00:00:00", "type": "temperature", "reading": "21" }
{ "date": "20150101T00:00:00", "type": "windspeed", "reading": "15" }
{ "date": "20150101T00:00:10", "type": "airhumidity", "reading": "51" }
{ "date": "20150101T00:00:10", "type": "temperature", "reading": "22" }
...

When this data is pushed into dashDB, it maintains a similar structure, i.e.

DATE              | TYPE          | READING
------------------+---------------+---------
20150101T00:00:00 | temperature   | 21
20150101T00:00:00 | windspeed     | 15
20150101T00:00:10 | airhumidity   | 51
20150101T00:00:10 | temperature   | 22

However, I would like this data to be in a 'flatter' structure, i.e.

DATE              | TEMPERATURE   | WINDSPEED    | AIRHUMIDITY
------------------+---------------+--------------+-------------
20150101T00:00:00 | 21            | 15           | -
20150101T00:00:10 | 22            | -            | 51

How can I flatten the SDP populated data?

Best How To :

One option is to create a Bluemix service that runs SQL code on a cron timer to move the data around into your desired structure.

See here for an example project using spring boot and groovy.

Do values come into a Cloudant reducer in key order?

mapreduce,cloudant,reducers

It is not guaranteed that values come into the reduce function in key order when rereduce=false.

how to perform 'SELECT TOP X FROM TABLE' type queries with DB2 / dashDB

dashdb

You can achieve this query using the FETCH FIRST x ROWS ONLY statement, E.g. SELECT * FROM customers FETCH FIRST 1 ROWS ONLY ...

What are the differences between mobiledata and cloudant services?

cloudant,bluemix

There are three ways to use Cloudant on Bluemix from an iOS application: Use Cloudant directly by adding it as a service to your application. Use MobileFirst for iOS, the Data part specifically (currently beta). Use the Mobile Data cross-platform SDK. (1) and (2) allow you to use views within...

how to connect a Java app ( java code ) to cloudant?

cloudant,bluemix

you need to add a piece of code in CloudantClient.java file under source directory of your project. Please add these lines in CloudantClient class: String VCAP_SERVICES = System.getenv("VCAP_SERVICES"); JSONObject vcap; vcap = (JSONObject) JSONObject.parse(VCAP_SERVICES); cloudant = (JSONArray) vcap.get("cloudantNoSQULDB"); cloudantInstance = (JSONObject) cloudant.get(0); cloudantCredentials = (JSONObject) cloudantInstance.get("credentials"); you can also put...

How can I use my sql knowledge with Cloudant/CouchDB?

sql,couchdb,cloudant,cookbook,recipe

This community wiki page provides links to some SQL patterns showing how they may be implemented in Cloudant. As this is a community wiki feel free to add new Q&A links here. General database queries Joining two documents by key? How do I do the SQL equivalent of “DISTINCT” in...

how to prevent groovy RESTClient from url encoding a path with %2F codes in it?

groovy,couchdb,cloudant

This worked for me: import groovyx.net.http.RESTClient import static groovyx.net.http.ContentType.* import groovyx.net.http.URIBuilder import groovyx.net.http.HTTPBuilder def server = new RESTClient( 'https://myaccount.cloudant.com' ) def uri = new URIBuilder( new URI( server.uri.toString() + '/aaaa/xxxx%2Fyyyy' ) ) response = server.get ( uri: uri, requestContentType: JSON ) ...

How can I securely connect to Cloudant using PouchDB?

android,ios,couchdb,pouchdb,cloudant

One option you may like to consider is implementing a service (e.g. running in the cloud) for registering new users of your app. Registration logic could look something like this: The handset code communicates with your application service requesting registration of the user The service makes a call to Cloudant...

cloudant post bad request

javascript,json,post,cloudant

fixed by using data: JSON.stringify(pos) Thanks to Nico O for answer...

The pricing programs of hosted CouchDB providers do not make sense

couchdb,couchbase,cloudant,touchdb,iriscouch

You have a point and then again it does not matter. Why you have a point Indeed running a single /_all_docs request is only a single request returning all of your documents. You just found a way to cheat you host into giving you a 'free service'. Why it does...

HTTP requests have increased considerably since enabling continuous replication

cloudant

Yes, it is possible that continuous replication is the cause of this step increase. Continuous replication does result in quite a few network calls between the source and target databases. To verify that your continuous replication is a major contributor to your HTTP usage, you could disable your replication for...

how to get password while replicating data in cloudant bluemix?

cloudant,bluemix

while replicating the database, cloudant doesn't take bluemix password. You can simply follow these steps here: 1) go to your dashboard and clock on your app. 2) Cloudant NoSQL DB should be listed as a service in your app. 3) click on show credentials 4) it will give you a...

Able to connect to Cloudant database, but cannot list contents from Bluemix Node.js app

node.js,api,bluemix,cloudant

The problem is that the code was using a single database API key to try and list all databases for a user. By changing cloudant.db.list to databasename.list (where 'databasename' is the name of the DB the API key was generated for), it worked fine. More info here.

Do 'reduce' with results from Cloudant search?

lucene,couchdb,cloudant

You can't reduce but you can use faceting to get counts. Example query ?q=*:*&counts=["type"] Example response { "total_rows":100000, "bookmark":"g...", "rows":[...], "counts":{ "type":{ "sofa": 10, "chair": 100, "lamp": 97 } } } https://docs.cloudant.com/search.html#faceting...

CouchDB (Cloudant) - get unique results for a “starts with” search on multiple fields

mapreduce,couchdb,unique,cloudant

I'd suggest using a search index for this. How do you generate Name from FirstName and LastName? Assuming they are concatenated, your index would look something like: function(doc) { if(doc.userDetails.email) { index("email", doc.userDetails.email); } var name = doc.userDetails.firstName + " " + doc.userDetails.lastName; name = name.trim(); if(name) { index("name", name);...

An internal error has occurred. The application may still be initializing or the URL used is invalid

bluemix,dashdb

The problem seems to be a cookie caching issue. I resolved the issue by using a different browser. Another option is to clear the browser cookie cache.

Are there any known negatives to using Requests in Flask to interface to Cloudant on Buemix?

python,flask,couchdb,bluemix,cloudant

The main advantage of using a Cloudant/CouchDB library is that you write less code. This can be significant in languages like Java where Rest and JSON handling is very cumbersome. However working with Rest and JSON in python using standard libraries is very easy. However, the main disadvantages of using...

How to combine multiple CouchDB queries into a single request?

couchdb,cloudant

There doesn't seem to be a way to achieve this with Cloudant Query at the moment. However, you can use a view query instead using the index created with Cloudant Query. Assuming the index is in a design document named ae97413b0892b3738572e05b2101cdd303701bb8: curl -X POST \ 'https://youraccount.cloudant.com/db/_design/ae97413b0892b3738572e05b2101cdd303701bb8/_view/ae97413b0892b3738572e05b2101cdd303701bb8?reduce=false&include_docs=true' \ -d ' {...

Build stats function on date

couchdb,cloudant

CouchDB and Cloudant allow the indexing of dates as compound keys in a MapReduce index e.g. generating keys like this: [ 2014, 5, 21] where each element of the array represents the year, month and day of your stored date, respectively. This can be achieved in map function like so:...

couchDB - how to display more than key/value?

jquery,json,nosql,couchdb,cloudant

1. you have a space after your "url -> ". Remove it and it will work. 2. emit accepts only 2 parameters but you can do something like: function(doc) { emit(doc.title, [doc.url, doc.tags]); } Or even better: function(doc) { emit(doc.title, {"url": doc.url, "tags": doc.tags}); } The first view will give...

Is it possible to link Couchbase Gateway Sync to a Cloudant server?

couchdb,couchbase,cloudant

As of this posting, sync_gateway only works with couchbase products directly, out of the box and supported. The source is open though and you could modify it if you are so inclined.

Connecting a Java application to an SQL database in IBM Bluemix

java,sql,database,bluemix,cloudant

None of these answers ended up working for me. I found no real solution so I switched to Node.js and got it working in minutes. I'd recommend using Node if you're using BlueMix. It's simpler.

Cloudant does not not store object data sent via Bluemix “Data for iOS 8”

ios,bluemix,cloudant

Can you provide the source code for the TestData class? For swift classes, String must currently use the NSString (ObjC) instead of the Swift String (e.g. var make:NSString)

Cloudant: How to create an index for “Sort” function?

couchdb,cloudant

The error message is telling you that there is no index to perform the sorting, or at least it can't find one. To help it find one, sort on customer and then on time, like so: curl -X POST 'https://<user>:<pass>@<user>.cloudant.com/<DB-name>/_find' -d ' { "selector": { "customer" : "123" }, "sort":...

Cloudant / Bluemix geo search

geo,cloudant,bluemix

It looks like the code is incorrectly specifying the limit and sort parameters as part of the search query (q). The following should generate the correct URL, I think: app.get('/search', function(request, response) { var latitude = request.query.latitude; var longitude = request.query.longitude; var qString = 'status:[1 TO 1]'; var sortString =...

Cloudant: Searching across databases

search,cloudant

It is not possible to perform joins across databases using CouchDB or Cloudant. You will need to either: put all your data in a single database and query that have separate databases and replicate the data from each to a single database and query that have separate databases and perform...

Connect to Cloudant via Excel Macro

excel,vba,excel-vba,cloudant

In case anyone else ever searches for this, I figured I would upload an actual response to this question (nearly a month later) rather than just a "yes, this is possible". Since Cloudant requires Basic Auth, the way that I have found to do this is below: Set objHTTP =...

how to 'flatten' the table in dashDB created by the schema discovery process (SDP)?

cloudant,dashdb,cloudant-sdp

One option is to create a Bluemix service that runs SQL code on a cron timer to move the data around into your desired structure. See here for an example project using spring boot and groovy....

Separate Cloudant Service from Bluemix

mongodb,heroku,cloudant,bluemix

You can surely create your own separate Cloudant account, and then enter all of its settings into your Bluemix app manually. The connection speed would depend on your choice of data center locations (SoftLayer, Rackspace or Azure) when you created your Cloudant account. If you choose a SoftLayer data center,...

Cloudant Search: Is it possible to weight documents?

search,cloudant

You can set the boost field in the options parameter of the index function. See http://docs.cloudant.com/api.html#index-functions.

Executing DDL in compound SQL using DashDB (DB2)

sql,db2,dashdb

You need to use dynamic SQL to execute some DDL statements: EXECUTE IMMEDIATE 'CREATE TABLE test AS (SELECT...' ...

Msgpack on Cloudant

cloudant,msgpack

So, I get a response from Cloudant, it's simply not possible to use MsgPack to transfert my datas. It looks like 'Content-Type:application/msgpack' is not supported by Cloudant and there is no development work currently being done to do so. Sorry for any inconvenience this may cause. It looks like there...

How can I use spring RestTemplate to make a test request against the Cloudant API?

resttemplate,cloudant

The following example shows you how you can make a basic call against Cloudant using Cloudant Education's example database. import org.springframework.web.client.RestTemplate; public class Application { private final static String URL = "https://education.cloudant.com/animaldb/_design/views101/_view/latin_name?include_docs=true"; public static void main(String args[]) { RestTemplate restTemplate = new RestTemplate(); // Normally you would bind the response...

how to toggle 'Include Docs' more easily inside the cloudant dashboard?

cloudant

I created a javascript bookmarklet: javascript:(function(){ if(document.location.toString().contains('include_docs=true')){ document.location=document.location.toString().replace('include_docs=true', '') } else { if (document.location.toString.contains('?') { document.location+='&include_docs=true' } else { document.location+='?include_docs=true' } } }()) When I want to toggle the 'include_docs' on or off, I can simply click on this bookmark in my bookmark toolbar. Note: I have only tested this...

Sync views between pouchdb and couchdb

node.js,couchdb,pouchdb,cloudant

pouchdb-find is a reimplementation of Cloudant Query Language, not their search index (which is what I think you're talking about). It's also not done; I've only written about half of the operators. :) You may also want to try the pouchdb-quick-search plugin, which is for full-text search. In general, the...

How to get Couchdb/Cloundant design document ready for search all fields?

couchdb,pouchdb,cloudant

First off, apologies that you've found this confusing. Cloudant Query is a relatively new feature in Cloudant/CouchDB and is really a wrapper around the existing, lower-level, indexing mechanisms (map/reduce and search). A user-created map/reduce or Cloudant Search index cannot be used to service a Cloudant Query call - it maintains...

continous replication on multi-tenant account using too much of my http usage

cloudant

One option is to setup is to write a small cron type service (e.g. in AWS or Bluemix) that uses the Cloudant HTTP Replication API to create one off replications at set intervals (e.g. hourly, or whatever period the business deem acceptable).

How to get a “fieldcount” (like wordcount) on CouchDB/Cloudant?

javascript,mapreduce,couchdb,word-count,cloudant

To get a count of each key in your document, create a "map" function like this: function (doc) { var keys = Object.keys(doc); for(var i in keys) { emit(keys[i], null); } } and use the built in _count reducer. You can then retrieve the grouped answer by accessing the view...

Viewing all documents on IBM Cloudant through API

ibm,cloudant

If you want to display the contents of the document append the following to the query string. include_docs=true You can also use this on Views and Search indexes to get the complete doc....

SQLCODE=-911 : “warehouser_error_message”: "File <>.csv.zip could not be loaded due to an exception in dashDB

cloudant,dashdb,cloudant-sdp

The SDP locks the database during the initial load. The -911 error indicates there is a lock contention issue. Ensure you aren't performing any other operations on the database table that is being loaded by the SDP. For find the current load status, see this question: How can I tell...

How can I connect to Cloudant from a Flask App running in Bluemix?

python,flask,bluemix,cloudant

The steps I followed to make the flask sample project work: Follow the instructions in the sample project README and deploy your code to Bluemix Login to the Bluemix console and add a Cloudant service to your application Modify the welcome.py and requirements.txt source code to connect to Cloudant. (see...

How do I do a basic indexed sum in Cloudant map/reduce?

mapreduce,cloudant

Try applying the group=true param on your reduce. "If you want to return results per key, use group=true. group=true is an invalid for a map-only or reduce=false view, you will get an error if you try to group a non-reduced view." https://cloudant.com/for-developers/views/...

cloudant python https connection pooling?

python,couchdb,gunicorn,cloudant

Requests uses urllib3 for its connection pooling, which is threadsafe. So as long as you don't call any methods on account which change its state (or only do so before you start making requests) you should be fine.

PouchDB - start local, replicate later

couchdb,replication,pouchdb,cloudant

It depends on what kind of data you want to sync from the server, but in general, you can replicate a pre-existing database into a new one with existing documents, just so long as those document IDs don't conflict. So probably the best idea for the star-rating model would be...

How to write query to my Cloudant database?

nosql,bluemix,cloudant

you can change your view into function(doc) { if(doc.user_tracking_id !== null){ emit([user_tracking_id, user_permit_doc_id]); } } and then query using the complex key [1, 10]...

How can I debug problems with warehouse creation?

cloudant,dashdb,cloudant-sdp

Take a look inside the document inside the _warehouser database, and look for the warehouser_error_message element. For example: "warehouser_error_message": "Exception occurred while creating table. [SQL0670N The statement failed because the row size of the resulting table would have exceeded the row size limit. Row size limit: \"\". Table space name:...

How can I tell if my SDP process is still running the 'initial' load?

cloudant,dashdb,cloudant-sdp

Log in to the Cloudant dashboard and select the _warehouser database. Inside that database, select the document that represents the SDP that you are trying to verify the status of. Inside that document, look for the tag sub_status: "replication_status": { "yourdatabase": { ... "sub_status": "initial", ... } }, This will...