Querying Inside Postgres JSON Arrays

Postgres JSON support is pretty amazing. I’ve been using it extensively for storing semi-structured data for a project and it has been great for that use case. In Postgres 9.3, the maintainers added the ability to perform some simple queries on JSON structures and a few functions to convert from JSON to Postgres arrays and result sets.

One feature that I couldn’t figure out how to implement using the built-in Postgres functions was the ability to query within a JSON array. This is fairly critical for lots of the reporting queries that I’ve been building over the part few days. Suppose you have some JSON like this, stored in two rows in a table called “orders”:

If you want to run a query like “find all distinct IDs in the products field”, you can’t do that with the built in JSON functions that Postgres currently supplies (as far as I’m aware!). This is a fairly common use case, especially for reporting.

To get this work, I wrote this simple PgPL/SQL function to map a JSON array.

What this function does is given a JSON array as “json_arr” and a JSON path as “path”, it will loop through all elements of the JSON array, locate the element at the path, and store it in a Postgres native array of JSON elements. You can then use other Postgres array functions to aggregate it.

For the query above where we want to find distinct product IDs in the orders table, we could write something like this:

That would give you the result:

Pretty cool!

Volatility of Bitcoin Index

A while back when I was a research assistant at the Federal Reserve, I worked on a project to make exchange rate volatility indexes for the major world currencies. We basically had some high frequency data for USD/EUR, USD/CHF, and USD/JPY and wanted to see how the financial crisis affected volatility. With all of the hype and turmoil around Bitcoin, I though it would be interesting to make a similar index for the Bitcoin/USD exchange rate.

Before Bitcoin is ever able to become a viable “currency”, volatility needs to come down a lot. Low volatility isn’t sufficient for it to take off, but is probably is necessary. If you take the traditional definition of a currency as a “store of value”, a “medium of exchange”, and a “unit of account”, persistently high volatility is absolutely a death knell. This is especially true in Bitcoin’s case where there is no government backing and there are attractive substitutes in traditional currencies.

One of the cool things about Bitcoin, however, is that lots of the data is fairly open. Most of the rest of the financial market data in the U.S. is behind the copyright lock and seal of the major exchanges and electronic trading networks. Both the NYSE and NASDAQ make lots of money off of selling market data, and they recently won a court case to raise their rates even further. That makes doing this kind of analysis on other securities an expensive endeavor for armchair quarterbacks like myself!

Bitcoin, however, has a rather open ethos and most of the exchanges publish realtime or near-realtime price data for free. CoinDesk aggregates this into their Bitcoin Price Index, which they graciously agreed to send over for this analysis.

The Volatility of Bitcoin Index (VOB Index—I’m open to suggestions on the name) is a simple rolling standard deviation of minutely log-returns. That is not nearly as complicated as it sounds. To create the index, I started with a time series of minutely Bitcoin price data and calculated the log returns (basically percent increase or decrease for each minute).  Then for each period, I took all of the returns within a trailing window and computed the standard deviation. I did this for three window lengths: 30, 60 and 90 days, and then annualized it. The Bitcoin markets are 24/7/365, so there are no holiday or weekend adjustments, which makes things a bit easier.

Here’s what the index looks like for the period August 2011 to January 31, 2014.

Volatility of Bitcoin IndexHere’s the BPI (Bitcoin Price Index) over that period.

BPI Index
Bitcoin volatility looks like it is dropping over time, especially from the early days in 2011 and 2012, except for a large bump in volatility in April of 2013. That was probably sparked by problems at Bitcoin’s largest exchange, Mt. Gox during that time period. This decrease is despite an increase in speculative interest in Bitcoin and moves by China to curtail the currency’s use.

Whether Bitcoin takes off is anyone’s guess and predicting if it does is probably a fool’s errand. There are lots of smart people working on it, but big questions about its viability still remain. In either case, it should be exciting to watch!

Tech Predictions for 2014

This is a just a bunch of fun tech predictions for 2014. I can’t claim any insider knowledge, but it will be interesting to look back on them at the end of the year to see what the outcomes are!

Bitcoin will become ”legitimate”, but not widespread

In 2014, regulators will likely issue guidance on how exactly companies and people can deal in Bitcoin without running afoul of anti-money laundering and money transmitter regulations, and how it is to be taxed. This will “legitimize” Bitcoin, but because of the difficulty of using the cryptocurrency and the insane amount of volatility of Bitcoin against fiat money, it will not become mainstream in 2014. Instead, Bitcoin will be relegated to particular types of transactions that the mainstream banks are either not accepting (due to the high probability of fraud or high compliance costs) or are charging exorbitant fees to facilitate. These will likely be cross-border transactions, especially in countries with capital controls (Argentina), legally grey-area transactions, or transactions that the major payment processors don’t accept.

Docker plus Deis or Flynn will result in an ”open-source Heroku” and lots of dev-ops innovation

Heroku is probably one of the biggest dev-ops innovations in the last five years, making it vastly easier to deploy web applications, freeing development teams to actually build applications instead of focusing on DevOps. Heroku’s 12 Factor App architecture is now widely used and 2014 will see that continuing. However, Heroku has a few problems.

First, There currently isn’t a good way to “build your own Heroku” out of open source components. If Heroku is too constraining, you are forced to spin up your own servers on Amazon Web Services for part of your application, which eliminates lots of the advantages Heroku brings to the table. Last year there was a ton of excitement about Linux containers (LXC) and Docker, which is abstraction on top of LXC that makes them easier to manage. Both Heroku and Google Cloud use Linux containers internally, but they are closed-source implementations. Docker will likely begin to change that this year.

However, Docker alone is not a Heroku replacement. Much of the innovation in Heroku lies in the build packs and routing mesh, which Docker does not provide. Two other open source projects aim to become that layer on top of Docker, and these are the ones I’m most excited to watch. The first is Deis, which seems to be the furthest along in creating an open-source Heroku. Deis has both a routing mesh and an application layer created as well as scripts to automatically scale your “personal Heroku” on AWS, Digital Ocean and Rackspace. Flynn has many of the same goals, but doesn’t appear to be as far along. Deis has commercial support from Opsware, while Flynn is raising money Kickstarter-style to build out their platform. In any case, while Heroku is great, it is very exciting to see open source competitors come to the scene.

AngularJS and EmberJS will win the front-end wars for web apps

For highly interactive web apps, both AngularJS and EmberJS will become the clear choices in 2014. Backbone, Knockout and other front-end JS frameworks will see declining usage simply because they don’t provide as much of a framework as AngularJS or EmberJS. For new sites except for “CMS-like” apps, people will stop generating HTML on the server and push the page rendering to Javascript on the browser. Because of this, back-end frameworks will pivot towards being better REST API servers and focus less on their HTML template rendering abilities. The wildcard is “CMS-style” sites that need to be indexed by Google. While Google’s crawler can execute Javascript, content-heavy sites will still need a mechanism to serve HTML from the server for reliable SEO. This means that full-stack Rails apps will still be important in 2014. I think the writing is on the wall for this kind of app, however.

Mobile will continue to be ”write twice” for the highest quality

Unfortunately, while HTML5 is great, it still won’t deliver the highest quality apps on mobile in 2014. As a cost-saving measure, or for apps that don’t need lots of interaction, HTML5 will be a viable choice. However, to create the highest-quality mobile apps in 2014, we’ll still need to write them twice: once for Android and once for iOS.

Wearable tech won’t be mainstream; in fact, society will push back from being ”always connected”

Google Glass and the like will remain curiosities and not mainstream. In fact, I think that people are beginning to push back from being always connected to the Internet. Smartphone usage in many social situations is become a faux pas and the word “glassholes” has already been coined for people that wear Google Glass in public. That being said, we will see the Internet smartly integrated into more consumer products, including continued innovation in automobile technology and home automation. The key for the “Internet of things” in 2014 will be unobtrusive, discrete, and large value-add, which probably isn’t wearable technology in its current form.

This is a dirt road leading from a waterfall outside of Vang Vieng, Laos back into town. The photo was taken during the rainy season,  during a break in the afternoon showers.

Motorcycling in Laos

Last month I spent a week motorcycling around Laos with a friend, starting at the capital and heading north through probably one of the most scenic parts of Southeast Asia. This is still a fairly undeveloped part of the world, meaning empty roads, not many other tourists, and an unspoiled landscape. It also means the logistics of this trip were not easy, but the ride was definitely worth it. If you are interested in doing a trip like this, read on!

Preparing for the trip

Roads in Laos range from two lanes and decent blacktop on the main highways to muddy dirt tracks in the villages, so you will want to be a fairly proficient rider with at least some dirt biking experience before you go. We went early in the rainy season, which contributed to the mud, but even in the dry season, a lot of the smaller roads will require some dirt riding skills.

The two most sensible starting points for a trip like this are the capital, Vientiane, and Luang Prabang, a UNESCO World Heritage Town about 340km to the north. Both cities have international airports, but they are very small, so expect to fly through a major Asian hub like Bangkok or Seoul if coming from the U.S. or Europe. You can also take a bus from Thailand, Cambodia or Vietnam if you are already in Southeast Asia, but expect it to be an overnight bus (12 hours+) on mountainous, winding roads.

Renting bikes

We rented a Honda CRF250 and CRF230 from Remote Asia in Vientiane, who were excellent. The bikes were in great shape and Jim from Remote Asia was very helpful when we had a minor mishap (I lost an ignition key to my bike!). You really will want to rent bikes from someone who can also provide a cell phone and “roadside assistance”, and by that I mean basically translating via cell phone and recommending mechanics. English is not widely spoken in Laos and emergency services are non-existent, so you will want an English speaking contact in-country from your rental agency.

We had no issues with Laotian police (in fact we did not see any outside of Vientiane), so it would seem that you don’t strictly need an International Drivers License or a motorcycle license at all. Your rental bike should come with a Laotian number plate and turn signals, and riding with a headlight during the day is apparently illegal so should be avoided.

The route

Laos has a few excellent motorcycle routes, and the one we took seems to be the most popular. You can order an excellent map of Laos with elevation profiles, road conditions, and city maps from GT Rider in Thailand here. This route took us 6 days of riding at a fairly leisurely pace. There are three long days of riding (over 150km) and one day each spent in Vang Vieng, Ponsavan, and Luang Prabang that you can use to explore the area surrounding these cities.

In addition to the GT Rider map, you should also get an Open Street Map app for your smart phone. OSM actually had pretty good coverage of Laos, including some of the caves and waterfalls outside of the cities. For Android, try OSMAnd, which lets you download country maps for offline use.

Here’s a map of the route we took and details of each leg.

 

Vientiane to Vang Vieng, 154km

If picking up your bikes in Vientiane, likely your first stop will be Vang Vieng. The ride to Vang Vieng is 154 km and fairly easy and flat until the last 50km or so where there are some small hills. Leaving Vientiane is where you will probably encounter the most traffic of the trip (not much!), but within 50km, it drops off a lot.

Vang Vieng is a tourist town along the Nam Song river with lots of caving, waterfalls, and rafting available nearby. It is definitely worth at least one whole day exploring outside of the town. I think of the three towns we visited, Vang Vieng had the most spectacular scenery. The town is surrounded by a dramatic karst landscape that is even more surreal in the rainy season when the peaks are surrounded in fog. In Vang Vieng, we went to a cave, waterfall and “blue lagoon” (swimming hole) on our one full day there. These were all found on dirt paths leading out from the town, for which Open Street Map was a real help locating.

Vang Vieng to Ponsavan, 233km

The second long day of riding, Vang Vieng to Ponsavan, is 233 km of challenging riding with tons of very steep switchbacks and incredible views of some of the highest peaks in Laos. On this part of the ride, you are heading to Ponsavan, which is the capital of the providence containing the Plain of Jars, a neolithic archeological site with large stone jars (some over 4 feet tall). The Plain of Jars was also bombed heavily during the Vietnam War as part of the CIA’s covert war in Laos, so there is lots of war history in this area as well. Ponsavan has a few NGOs operating to clear unexploded ordinance that have interesting exhibits in the town on the war.

Ponsavan to Luang Prabang, 259km

The last long day of riding will take you 259 km from Ponsavan to Luang Prabang (backtracking to Pho Khoun, then north to Luang Prabang). Backtracking in this way isn’t all that bad because the scenery and riding is so great heading from Pho Khoun to Ponsavan, you’ll have no problem doing it twice.

Pho Khoun makes a good stopping place for lunch, as its about at the midpoint of this ride and there are a few places to refuel and grab lunch. From Pho Khoun, you’ll have similar riding (and that’s to say great!) to Luang Prabang.

Luang Prabang is a UNESCO World Heritage Site and has by far the best developed tourist infrastructure of the three cities we visited. Luang Prabang is at the intersection of the Mekong River and the Nam Khan River and has a large temple in the center of the city with great views of both rivers. There are waterfalls about 20km outside of town as well as rafting, elephant tours, and kayaking if you choose to stay here for a few days.

Route: Vientiane - Vang Vieng- Pho Khoun - Ponsavan - Phou Khoun - Luang Prabang

Elevation Profile: Vientiane – Vang Vieng – Pho Khoun – Ponsavan – Phou Khoun – Luang Prabang

Using NGINX to proxy TileStache

I’m working on a re-rendering of OpenStreetMap for hiking, with hill shading and topographic lines and I decided to use TileStache to render the tiles. TileStache has the nice ability to render tiles from a bunch of different sources and then overlay them with different amounts of transparency and layer masks (just like Photoshop). TileStache is a Python WSGI server that you can run in mod_python or GUnicorn to serve tiles directly over HTTP. TileStache can cache map tiles to the file system and serve the static PNGs if they exist or render them from scratch using Mapnik if they don’t. Its pretty fast, especially if the tiles are pre-rendered.

However, GUnicorn is a pre-forking server. This means that it needs to fork a different process for each client connection. What happens if a slow client connects is that TileStache processes are rapidly used up to serve that client (typically clients make up to 8 separate HTTP connections for slippy maps, resulting in 8 processes each!). This is the case even if the tiles are being served from cache.

What you need to do is add a reverse proxy in front of GUnicorn, using something like NGINX. The reverse proxy using an evented IO model, which enables it to manage sending data back to a slow client without using an operating system process. NGINX can also directly serve static assets from the filesystem, which means we can serve the cached tiles without even hitting GUnicorn/TileStache.

Getting this to work requires a bit of NGINX HttpRewriteModule voodoo, though. The issue is that TileStache saves cached tiles in a slightly different path than the URI path that comes in via HTTP. Say you have a OpenStreetMap-style URL like this: myserver.com/tiles/layer/$z/$x/$y.png. In this URL, $z is zoom level (usually 1-19), and $x and $y are tile coordinates. For higher zoom levels, you can have 10,000+ by 10,0000+ tiles in the x and y directions. That’s way too many PNG files to store in one folder on the filesystem. So, TileStache splits up the x and y paths into two levels. Say you have a URL like /tiles/layer/7/12345/67890.png. TileStache will store that in the filesystem path /tiles/layer/7/012/345/067/890.png. Notice how 12345 is broken into 012/345? That means that there will be at most 1,000 files or folders in each directory—a manageable amount. The issue is we need to get NGINX to rewrite URLs to server these static assets. Here’s how I accomplished that:

This mountain of rewrite lines will rewrite the request URL to the filesystem format, then look for tiles in the filesystem tree starting at /osm/cache. The last line tells NGINX to look for the rewritten URL, then if the file is not found, to send the request to the @tilestache; location block, which looks like this:

That location block proxies the request to the GUnicorn server listening on localhost:8080.

This seems to be working great. NGINX is far faster in serving static assets, and if all of the worker TileStache processes are busy rendering, the cached zoom levels of the map work fine!

Link

Craigslist is on board: OpenStreetMap soars to new heights

Craigslist is now using OpenStreetMap for showing the location of apartment listings! This is great for two reasons. First, looking for apartments will become much easier on Craigslist. Second, it’s validation that OpenStreetMap’s dataset is hugely valuable and robust enough for commercial use. Hopefully, I’ll be rolling out my OpenStreetMap project within the next week or so. The project is a re-rendering of the basic slippy map with topographic lines and hiking trails taking more prominence. More to come!

Backbone.js: Thankfully, a great MVC framework for the frontend

Frameworks, frameworks…

On the backend, web development frameworks have been growing quickly in popularity. Rails, Django, CakePHP, and others are popular because they save developers a ton of time. Someone once said that a good developer is a lazy developer, and frameworks enable developers to kick back with a Corona on a beach (well, not quite, but close) by making a lot of the architectural decisions for the developer. Rails is a great example of this, with a clear MVC structure, preset file system hierarchy, and even database schema conventions. If you were coding a site in pure Ruby, you’d need to make all of these decision yourself.

While backend frameworks are really popular, there has been a dearth of good choices in front end frameworks. As web apps are moving more processing to client-side Javascript, this was becoming a growing problem.

The front-end Javascript jungle

Front-end javascript tends to be a huge mess of jQuery callbacks, DOM elements stuffed with extra object properties, and a polluted root window object. As client-side applications are get larger, this is completely unsustainable. It makes code hard to maintain, hard to understand, and un-testable with unit testing libraries. Backbone.js greatly helps with all of these issues.

Enter Backbone.js

Backbone is a minimalist MVC framework for Javascript. It adds models and collections with persistance code that works well with JSON REST APIs, as well as views that automatically re-render on model updates and controllers that handle hash-bang URLs or HTML5 pushState.

All of this comes in 4KB of minified Javascript that ties in with jQuery, Underscore, or any other Javascript library.

Backbone dos and don’ts

I’m currently working on a small side project to brush up on some Javascript coding, and decided to use Backbone as a front-end framework (I’m using Rails on the backend). Here’s some brief notes from a my first impressions:

Do

  • Put most of your front-end application (or business) logic in the models. This is basically the same thing you would do with a MVC app on the server.
  • Use a templating library. Underscore.js has a pretty decent _.template() function. HTML in really clutters your Javascript code.
  • Try using the Rails asset pipeline or some other way to minify and compile your JS. This way, you can spread your Backbone code out into many files. I tended to use a folder hierarchy similar to Rails (folders for models, collections, controllers, and views).

Don’t

  • Put much logic in your views. It is very hard to debug view code because the function that renders the view is typically created programmatically by the templating library.
  • Don’t prematurely optimize your view code. The easiest way to render a view is to just create an HTML fragment from a templating library then insert it into the DOM with jQuery. This is fine for most cases. You can also manipulate the DOM by changing the inner text of elements on a view re-render, which might be faster but often isn’t worth the extra work.

Using Capistrano to deploy Rails apps from Gitolite

Here, I’ll show you how to deploy a Rails app from a Gitolite repository via Capistrano. In this example, I’m running a Phusion Passenger on NGINX on Ubuntu 10.04. The instructions should be very similar for Ubuntu 12.04.

First, understand what we’re doing here. I’m assuming you are using Gitolite for version control (although similar instructions would probably work for Github). We’re going to add a read-only deployment key to the Gitolite repository. When you run cap deploy, Capistrano will log into your production server via SSH (using public key authentication). Then the Capistrano script will instruct the production server to check out the latest version of your app from the Gitolite repository into a directory on the production server. Finally, the Capistrano script will change a symlink to the new version of your app and instruction Phusion Passenger to reload the app into memory on the next hit.

Setting up your production server

Create a new user for deployment-related tasks on your production server. Switch to that user.

Now, generate some SSH keys for that user. Run as the deployuser:

I don’t typically enter a password for this keypair. The reason is that this keypair is only used for read-only access to your code repository in Gitolite. If your code is highly sensitive, you might want a password. If you enter one here, you will be prompted for it each time you deploy code.

Now, wherever you have your Gitolite admin repository checked out, open it up and add the public key to your keydir folder. I like to keep my deployment keys in a subfolder called something like “deployment”.

Say, for example, your Gitolite admin repository is at ~/repos/gitolite-admin. Switch to that path. Now enter the folder keydir. Make a new subfolder called deployment, and then a new file in that folder called something like MyDeploymentKey.pub. Open that file in your editor and paste the public key that you just created from your deployment server. Typically, that key is found at ~/.ssh/id_rsa.pub.

Now, open your gitolite.conf file (in the conf folder in your Gitolite repository). Find your project and add a directive to grant your deployment key read-only access. Here’s an example project section:

Note that even though the deployment key could be in a subfolder, you still just enter the filename minus the “.pub”.

Save the Gitolite files, commit and push to your Gitolite server.

Setting up Capistrano

Now, open up your Rails project you want to deploy. Add these gems:

Run bundle install and then from the top directory of your project, run capify .. This adds Capistrano to your project. Open up config/deploy.rd and add something like this:

This deploy script will checkout your code from the project myproject on mygitoliteserver.com and deploy it to /srv/mydomain.com/public on your production server (make sure you create this directory). Whenever you deploy, Capistrano will touch tmp/restart.txt so that Phusion Passenger restarts with the new code.

Once you are finished editing this script, commit your changes, push your latest code to your Gitolite server.

Deciding who gets to deploy

For each user you want to allow to deploy code, have them generate a SSH key. On your deployment server, open or create ~deployuser/.ssh/authorized_keys. For each user you want to allow to deploy, add their public key (one key per line) to this file.

Deploying!

Now, to test out deployment, run from your Rails root on your development machine (the machine that has the SSH key you added to ~deployuser/.ssh/authorized_keys), run cap deploy.

5 Government datasets for building web apps

Recently, I’ve gotten interested in using some open government data to build useful web applications. There has been a bit of work in this space, with many Federal agencies sponsoring app contests for developers to create products that show off the agencies’ datasets. Challenge.gov is the central listing of these app contests; take a look if you are interested in seeing what kind of challenges are open for entries. I’ve had a bit of experience working with government data and here’s some of the most interesting datasets that I’ve found so far.

U.S. Census TIGER

This is the mother of all mapping datasets in the U.S. It contains most roads, populated places, and administrative boundaries (for counties and states). There is also a ton of demographic data available subset by geographic location. These are huge datasets, and would need specialized GIS knowledge to do much with. The OpenStreetMap project has imported most of the roads and administrative boundary data, making the U.S. portion of their map pretty detailed. One issue with TIGER data is that many of the roads in the dataset no longer exist. This is especially true in areas that have lost population, like abandoned towns or areas converted to national parks.

Federal Reserve Economic Data (FRED)

Almost any conceivable economic indicator for the U.S., going back as far as government data exists. Want to know the USD/GBP exchange rate in 1974? Got it. CPI for 1951? No problem. There are also a lot of useful graphing tools built into the site and a nice REST API for accessing the data from web services.

NOAA Weather Radars

Historical RADARs back to the 1990s and near real-time radar images (10 minute or so delay). This website also has satellite images back to the 1970s. I’m sure there is some kind of really cool big data project you could create with these. Maybe show the historical likelihood of rain in a particular location on a particular day of the year, or check historical satellite images to determine when is the best change for a sunny day.

Securities and Exchange Commission EDGAR

Near real-time display of all filings for publicly-traded companies in the U.S. New share issuance,  corporate governance changes, and quarterly reports are all there.

USGS Stream Flow

I’m mostly putting this on here because I check it frequently for kayaking. This site has near real-time data on the flow and temperature of many rivers in the U.S. The data comes from gauging stations operated by USGS.

 

Using TSDB to store U.S. Equities data from TickData

In this article, I’ll show you how to use my Time Series Database to import some sample U.S. equities data from TickData, a data provider of high-frequency historical financial data. First, get the sample data and unzip it on your computer. Look in the Quotes folder for a file called GLP_27667.asc. This is file has quotes for Global Partners LP, a petroleum company. If you are running on Mac OS X or Linux, you can use the head command to see the first few lines, which in my download look like this:

If you look on TickData’s website, you can find a document describing the format of these files in detail, but for this example, we’re only going to look at the first 7 fields.

  • Date (MM/DD/YYYY)
  • Time
  • Exchange indicator (P means ARCA, M means Chicago, T means NASD)
  • Bid Price
  • Ask Price
  • Bid Amount (100 share lots)
  • Ask Amount (100 share lots)

Let’s create a new TSDB file to store this data. To do this, use the tsdbcreate command.

This command is creating a new tsdb database called glp.tsdb with a series called “quotes”. That series has 5 columns: exchange (a character), bid_price (floating point double), ask_price (floating point double), bid_size and ask_size (both 32-bit integers).

You can add more than one series to a TSDB file. If you were to call the tsdbcreate command and glp.tsdb already existed, then it would add another series to the file.

Now that you have an empty TSDB file, we’ll create some import instructions and import the data. TSDB has a program called TSDB import that uses an XML file to describe how to parse the delimited data in a file like a CSV file and append it to the end of a time series.  Whenever you import data, you are always appending it to the end of a time series. You can import from more than one file into one series in a TSDB file, but you need to start with the earliest file. You also must make sure each of the files you import are in chronological order from oldest timestamp to newest (repeated timestamps are okay).

Here’s an example XML file that shows how you could import the TickData quotes files.

The XML import tells the tsdbimport command how to map the comma-separated values in each line into columns in the database. Notice in the timestamp fieldparser block, we specify to use tokens 0 and 1 to make the timestamp. This tells the parser to join tokens zero and one with a space and then to parse the combined string as a timestamp.

You can save that XML as a text file called “instructions.xml” and then import the GLP_27667.asc file like this:

After you run this command, you’ll have the complete GLP time series imported. Now, you can access this data quickly via either the R or MATLAB TSDB bindings. Stay tuned for a article on how to do that within the next few days.