Id,InvoiceNum,InvoiceDate,BillTo,ShipTo
1,ABC123,2018-01-15 00:00:00.000,Lynn Hess,"Herman Trisler, 4189 Oak Drive"
2,XYZ987,2017-06-23 00:00:00.000,Yvonne Pollak,"Clarence Burton, 1470 Cost Avenue"
3,FOO777,2018-01-02 00:00:00.000,Phillip Freeman,"Ronda Snell, 4685 Valley Lane"Posts tagged with 'c'
This is a repost that originally appeared on the Couchbase Blog: CSV tooling for migrating to Couchbase from Relational.
CSV (Comma-seperated values) is a file format that can be exported from a relational database (like Oracle or SQL Server). It can then be imported into Couchbase Server with the cbimport utility.
Note: cbimport comes with Couchbase Enterprise Edition. For Couchbase Community Edition, you can use the more limited cbtransfer tool or go with cbdocloader if JSON is an option.
A straight relational→CSV→Couchbase ETL probably isn’t going to be the complete solution for data migration. In a later post, I’ll write about data modeling decisions that you’ll have to consider. But it’s a starting point: consider this data as "staged".
Note: for this post, I’m using SQL Server and a Couchbase Server cluster, both installed locally. The steps will be similar for SQL Server, Oracle, MySQL, PostgreSQL, etc.
Export to CSV
The first thing you need to do is export to CSV. I have a relational database with two tables: Invoices and InvoiceItems.
I’m going to export the data from these two tables into two CSV files. With SQL Server Management Studio, this can be done a number of different ways. You can use sqlcmd or bcp at the command line. Or you can use Powershell’s Invoke-Sqlcmd and pipe it through Export-Csv. You can also use the SQL Server Management Studio UI.
Other relational databases will have command line utilities, UI tools, etc to export CSV.
Here is an example of a CSV export from a table called "Invoices":
Here’s an export from a related table called "InvoiceItems":
InvoiceId,Product,Quantity,Price
1,Tire,2,20.00
1,Steering Wheel,5,10.00
1,Engine Oil,10,15.00
1,Brake Pad,24,1000.00
2,Mouse pad,1,3.99
2,Mouse,1,14.99
2,Computer monitor,1,199.98
3,Cupcake,12,.99
3,Birthday candles,1,.99
3,Delivery,1,30.00Load CSV into Couchbase
Let’s import these into a Couchbase bucket. I’ll assume you’ve already created an empty bucket named "staging".
First, let’s import invoices.csv.
Loading invoices
C:\Program Files\Couchbase\Server\bin\cbimport csv -c localhost -u Administrator -p password -b staging -d file://invoices.csv --generate-key invoice::%Id%Note: with Linux/Mac, instead of C:\Program Files\Couchbase\Server\bin, the path will be different.
Let’s break this down:
-
cbimport: This is the command line utility you’re using
-
csv: We’re importing from a CSV file. You can also import from JSON files.
-
-c localhost: The location of your Couchbase Server cluster.
-
-u Administrator -p password: Credentials for your cluster. Hopefully you have more secure credentials than this example!
-
-b staging: The name of the Couchbase bucket you want the data to end up in
-
--generate-key invoice::%Id% The template that will be used to create unique keys in Couchbase. Each line of the CSV will correspond to a single document. Each document needs a unique key. I decided to use the primary key (integer) with a prefix indicating that it’s an invoice document.
The end result of importing a 3 line file is 3 documents:
At this point, the staging bucket only contains invoice documents, so you may want to perform transformations now. I may do this in later modeling examples, but for now let’s move on to the next file.
Loading invoice items
C:\Program Files\Couchbase\Server\bin\cbimport csv -c localhost -u Administrator -p password -b staging -d file://invoice_items.csv --generate-key invoiceitem::#UUID#This is nearly identical to the last import. One difference is that it’s a new file (invoice_items.csv). But the most important difference is --generate—key. These records only contain foreign keys, but each document in Couchbase must have a unique key. Ultimately, we may decide to embed these records into their parent Invoice documents. But for now I decided to use UUID to generate unique keys for the records.
The end result of importing this 10 line file is 10 more documents:
What’s next?
Once you have a CSV file, it’s very easy to get data into Couchbase. However, this sort of direct translation is often not going to be enough on its own. I’ve explored some aspects of data modeling in a previous blog post on migrating from SQL Server, but I will revisit this Invoices example in a refresher blog post soon.
In the meantime, be sure to check out How Couchbase Beats Oracle for more information on why companies are replacing Oracle for certain use cases. And also take a look at the Moving from Relational to NoSQL: How to Get Started white paper.
If you have any questions or comments, please feel free to leave them here, contact me on Twitter @mgroves, or ask your question in the Couchbase Forums.
Cartridge cleaning is something that I need to do more of, now that I’m selling off most of my video game collection, one piece at a time.
I welcome your feedback, but here’s how I’m currently doing it for standard NES cartridges. The materials you need for cleaning should only set you back $20 (or less).
Open the cartridge
First, open the cartridge. The type of screws used in standard Nintendo cartridges are a little out of the ordinary. You will need a special "security bit". I have found that not all cartridges use this same screw, but most do. I got a new ratcheting screwdriver set as a gift, and one of the bits that came with it works perfectly. Here’s the screwdriver and bits that I've used for various screw types.
Your NES cartridge may have 3 or 5 screws. Be careful not to drop them after you unscrew them, as they are very tiny and easy to lose.
Carefully separate the two halves of the cartridge and bear in mind that there are two small latches at the top that clasp together.
Inside the cartridge you should see a small circuit board and a lot of empty space. In this example, I used Wizards and Warriors.
Note that the inside may vary. If you’re doing cartridge cleaning on older NES titles, it may just be a Famicom board with an adapter inside. You may see some cartridges that contain batteries. Make sure to be very careful when handling the board. You may want to ground yourself to avoid any static discharge.
Cartridge cleaning
The most important things to clean are the contacts that stick out the bottom.
Contacts
These are the gold/copper color strips that are the most exposed and actually plug into the console. To clean these, use isopropyl alcohol (I’m using 99%) and a lint-free cloth.
Note: most cartridges have a warning "DO NOT CLEAN WITH BENZENE, THINNER, ALCOHOL OR OTHER SUCH SOLVENTS." on the back. I suspect this warning is to a) get people to buy official Nintendo cleaning kits, and/or b) indemnify Nintendo if something goes wrong. Alcohol, especially alcohol that’s not very diluted, is perfectly fine for cleaning electrical contacts. You don’t have to take my word for it, check out here, here, and here for more information. I’ve also heard that certain types of erasers are good for cleaning these contacts. The key is make sure it’s absolutely dry before you plug in the cartridge.
First, put a very small amount of the alcohol on the contacts (you might want to use a dropper, a Q-tip, or just dip the cloth into the alcohol), and rub them with a cloth (make sure to get both sides).
You don’t need very much alcohol.
Plastic case
Next, while this is drying, you may want to perform cartridge cleaning on the rest of the exposed portion of the cartridge with soap and water. Use a toothbrush or other gentle cleaning brush to get all the corners. This part of the cartridge is usually fine if stored correctly. However, since it’s exposed, who knows what might have gotten in there over the years. Dust, spilled drinks, spit from people blowing into them, and so on.
Finally, you may want to clean up the outside of the cartridge for aesthetic reasons. The line of ridges can often build up dust and grime, so you can also use soap and water on that. Be careful not to get anything on the label.
Make sure to dry the soap and water with a cloth. I recommend not using a paper towel.
If there are any stickers or other residue on the outside of the cartridge, you can use something like Goof Off to remove it. Again, be very careful not to get this on the label.
Make sure everything is dry before putting it back together. I cannot overstate this. Do not mix moisture and electricity!
The circuit board should only fit one way back in the case.
All done!
The cartridge I used in this example was a fairly standard NES cartridge and was not in bad shape to begin with. If I run into any other interesting examples, I will write separate blog posts.
Cartridge cleaning, because cleanliness is next to… uh… something.
This is a repost that originally appeared on the Couchbase Blog: Test Drive: Trying Couchbase on Azure for Free.
Test Drive is an Azure feature that allows you to try out software in the cloud for free.
Previously, I wrote about how Getting started with Couchbase on Azure is easy and free. That post told you how to get started with $200 in Azure credit. With Test Drive, however, it’s even easier to get started, and you don’t have to use any of that $200 credit.
Test Drive on Azure Marketplace
The only thing you need before you start is a Microsoft account, which is free.
Start by going to the Couchbase Enterprise product page on Azure.
There’s a big "Test Drive" button. Simply click to get started.
Before you click to get started, notice that the Test Drive has a limited duration. You will get 3 hours to evaluate Couchbase Server.
Step by Step Evaluation Labs
We’ve prepared 4 step-by-step labs that you can follow to start evaluating Couchbase Server.
The test drive deployment is a fully functional Couchbase Server Enterprise cluster with all features enabled. The only limit you have is time.
Summary
Couchbase is proud to partner with Microsoft to make this test drive available to you.
If you are interested in learning more about Couchbase on Azure, please check out these other blog posts:
If you have any questions, comments, or suggestions, please leave a comment, email me matthew.groves@couchbase.com, or tweet me @mgroves on Twitter.
This has been a good year for Cross Cutting Concerns. I had some amazing guests on the podcast. The C# Advent was also way more successful than I anticipated. And I also made some important technical improvements to the site.
Google Analytics
I pulled the top 50 most viewed page from Google Analytics. The top 10 pages that were viewed the most this year are listed below. The C# Advent page got over double the views of the second place page. (The second place page is a post from 2014 on ASP Classic that baffles me with the amount of traffic it gets).
| Page | Pageviews |
|---|---|
| The First C# Advent Calendar (2017) | 5503 |
| Using HTTP/Json endpoints in ASP Classic (2014) | 2413 |
| Command/Query Object pattern (2014) | 2229 |
| How I use Fluent Migrator (2014) | 2010 |
| ActionFilter in ASP.NET MVC - OnActionExecuting (2012) | 1386 |
| Parsing XML in ASP classic (2014) | 974 |
| Visual Studio Live Unit Testing: New to Visual Studio 2017 (2017, Couchbase Blog repost) | 964 |
| AOP vs decorator (2012) | 954 |
| SQL to JSON Data Modeling with Hackolade (2017, Couchbase Blog repost) | 895 |
That's right, some of the most viewed pages on my site have to do with ASP Classic and XML. These are posts I did on a lark during a consulting gig back in 2014.
It always seems like the posts I do on a lark are the ones that take off. For instance, over at the Couchbase Blog, I believe I have the most viewed blog post of 2017 with Hyper-V: How to run Ubuntu (or any Linux) on Windows. This is a quick post I wrote as I was learning it myself, and it keeps raking in the views. It's #2 on Bing and Google when you search for "hyper-v ubuntu", so that helps.
I'm not just looking to raw views, though. I would like to have some measure of the quality of posts. If you know of any metrics that might help track that, please let me know. Google Analytics has a "Bounce Rate" which might be useful to look at. The 10 pages with the lowest bounce rate (out of the 50 most viewed pages) are all podcast posts!
I'm going to speculate and say that podcast pages have the lowest bounce rate because they a prominent and immediately useful call-to-action link (i.e. "listen to this podcast"). Excluding the podcasts, the top 10 links with the lowest bounce rate are:
| Page | Bounce Rate |
|---|---|
| A Coryat scorekeeper for Jeopardy (2014) | 82.35% |
| The First C# Advent Calendar (2017) | 83.30% |
| Autocomplete multi-select of Geographical Places (2014) | 85.39% |
| Lessons learned about Fluent Migrator (2014) | 85.92% |
| AOP in JavaScript with jQuery (2012) | 86.46% |
| Terminology: cross cutting concern (2012) | 86.58% |
| Adventures in Yak Shaving: AsciiDoc with Visual Studio Code, Ruby, and Gem (2017) | 87.42% |
| An Audit ActionFilter for ASP.NET MVC (2012) | 88.13% |
| Using HTTP/Json endpoints in ASP Classic (2012) | 88.95% |
Once again, ASP Classic appears, but it's interesting to see a mostly different set of posts here. The average bounce rate for the top 50 most viewed pages is 90.80%. So these all beat the average (if that has any meaning).
Podcast Analytics
I've done a poor job of tracking podcast analytics since I started the podcast. I assumed I could grab download numbers from Azure (where I host my podcast files), but that turns out to be incredibly painful. I mainly do the podcast for fun and because I want to talk to enthusiatic tech people. But in my attempts to get sponsorship, I quickly realized that I needed a better solution for analytics. I signed up for PodTrac, but only after season 2 was finished. So these numbers aren't going to be very impressive. Season 3 onwards should provide more useful analytics. The top 10 are the 10 latest podcasts that I published (which makes sense).
The #1 most downloaded episode based solely on my better late than never PodTrac analytics is #061 - Eric Elliott on TDD.
Tech improvements
I've made some changes to Cross Cutting Concerns to hopefully improve SEO and your experience as a reader/listener.
- HTTPS. I host this site on a shared website on Azure, so it's not exactly straightforward. But I used CloudFlare and followed this blog post from Troy Hunt.
- HTML Meta. I added Twitter cards, tagging, description, and so forth. This makes my posts look a little nicer on Twitter and search engines, and hopefully will improve my search rankings. If you want to see what I did, hit CTRL+U/View Source right now and check out all the <meta />
- If you clicked on some of the top 10 posts earlier, you might have noticed a new green box with a call to action. I've put this on some of my most popular posts to try and drive some additional engagement, page views, and podcast subscribers.
- Image optimization. pngcrush, gifsicle, and jpegtran losslessly optimize images so they are smaller downloads. This will help with my Azure bill a little bit, and also improve page speed. It's currently a manual process, so sometimes I will forget.
What's next?
Based on the analytics I'm seeing so far, I'm going to:
Continue:
- Reposting my Couchbase blog posts. These help drive traffic back to my employer's site and increase awareness of Couchbase. Which is my job!
- Podcasting. I'm enjoying it, some people are listening to it.
- Keep podcast episodes short. I get comments in person about how the length of the shows (10-15 minutes) is just right. I'm going to expand by a few minutes (see below), but episodes will not increase in length by much more than that (unless I feel like making a longer special episode).
- C# Advent. I've heard that this helps people get traffic to their blogs. I'm definitely happy with it, and it helps the C# / Microsoft MVP community. I'll start recruiting writers a little earlier in November 2018.
Start:
- Adding some more fun to podcast episodes. I've got an idea to add a little humor to each podcast. Stay tuned!
- Podcast sponsorship. I've lined up a sponsor for 6 months of episodes. Let's see how it goes. I'd like to use this money to buy better equipment, pay for hosting, and maybe even purchase tokens of appreciation for guests.
Stop:
- Tracking podcast downloads with Azure and FeedBurner.
- People from using ASP Classic. Somehow.
This is a repost that originally appeared on the Couchbase Blog: Scaling Couchbase Server on Azure.
Scaling is one of Couchbase Server’s strengths. It’s easy to scale, and Couchbase’s architecture makes an efficient use of your scaling resources. In fact, when Couchbase customer Viber switched from Mongo to Couchbase, they cut the number of servers they needed in half.
This blog post is the third in a (loose) series of posts on Azure.
The first post showed you the benefits of serverless computing on Azure with Couchbase.
The second post showed a concrete example of creating a chatbot using Azure functions and Couchbase Server.
The previous post only used a cluster with a single node for demonstration purposes. Now suppose you’ve been in production for a while, and your chatbot is starting to get really popular. You need to scale up your Couchbase cluster. If you deployed Couchbase from the Azure Marketplace, this is a piece of cake. Long story short: you pretty much just move a slider. But this post will take you all the way through the details:
-
Creating a brand new cluster with 3 nodes.
-
Scaling the cluster up to 5 nodes.
-
Scaling the cluster down to 4 nodes.
Create Couchbase Cluster on Azure
Assuming you have an Azure account, login to the portal. If you don’t yet, Getting Started with Azure is Easy and Free.
Once you’re logged in, click "+ New" and search for Couchbase Server in the marketplace. I’m using BYOL (bring your own license) for demonstration, but there is also an "Hourly Pricing" option that comes with silver support.
Once you select Couchbase, you’ll be taken through an Azure installation wizard. Click the "Create" button to get started.
Step 1 is the "Basics". Fill out the username and password you want for Couchbase, the name of a resource group, and a location (I chose North Central US because it is close to me geographically). Make sure to make a note of this information, as you’ll need it later.
The next step is Couchbase Config. There are some recommended VM types to use. I went with DS1_V2 to keep this blog post cheap, but you probably want at least 4 cores and 4gb of RAM for your production environment. I also elected not to install any Sync Gateway Nodes, but if you plan to use Couchbase Mobile, you will need these too. I’m asking for a total of 3 nodes for Couchbase Server.
After this, step 3 is just a summary of the information you’ve entered.
The last step is "buy". This shows you the terms. One "Create" button is all that remains.
Now, Azure will go to work provisioning 3 VMs, installing Couchbase Server on them, and then creating a cluster. This will take a little bit of time. You’ll get an Azure notification when it’s done.
You should have just enough time to get yourself a nice beverage.
Using your Couchbase Cluster
When Azure finishes with deployment, go look at "Resource groups" in the Azure portal. Find your resource group. Mine was called my_cb_resource_group.
Click on the resource group. Inside that resource group, you’ll see 4 things:
-
networksecuritygroups (these are firewall rules, essentially)
-
vnet (the network that all the resources in the group are on)
-
server (Couchbase Server instances)
-
syncgateway (Couchbase Sync Gateway instances. I didn’t ask for any, so this is an empty grouping)
First, click 'server', and then 'instances'. You should see 3 servers (or however many you provisioned).
Next, click 'deployments'. You should see one for Couchbase listed. Click it for more information about the deployment.
The next screen will tell you the URL that you need to get to the Couchbase Server UI (and Sync Gateway UI if you installed that). It should look something like: http://vm0.server-foobarbazqux.northcentralus.cloudapp.azure.com:8091.
Paste that URL into a browser. You will be taken to the Couchbase Server login screen. Use the credentials you specified earlier to login.
After you login, click on 'servers'. You will see the three servers listed here. The URLs will match the deployments you see in the Azure portal.
Let’s put some data in this database! Go to Settings → Sample Buckets and load the 'travel-sample' bucket.
This sample data contains 31591 documents. When it’s done loading, go back to "servers". You can see how the 'items' (and replica items) are evenly distributed amongst the three servers. Each node in Couchbase can do both reads and writes, so this is not a master/slave or a read-only replica sets situation.
Scaling up
Now, let’s suppose your application is really taking off, and you need to scale up to provide additional capacity, storage, performance. Since we’re using Couchbase deployed from the Azure marketplace, this is even easier than usual. Go to the Azure portal, back to the resource group, and click "server" again. Now click "scaling"
Next, you will see a slider that you can adjust to add more instances. Let’s bump it up to 5 total servers. Make sure to click "save".
Now, go back to 'instances' again. Note: you may have to refresh the page. Azure doesn’t seem to want to update the stale page served to the browser on its own. You will now see server_3 and server_4 in "creating" status.
You will need to wait for these to be deployed by Azure. In the meantime, you can go back over to the Couchbase Server UI and wait for them to appear there as well.
When adding new servers, the cluster must be rebalanced. The Azure deployment should attempt to do this automatically (but just in case it fails, you can trigger the rebalance manually too).
During this rebalance period, the cluster is still accessible from your applications. There will be no downtime. After the rebalance is over, you can see that the # of items on each server has changed. It’s been redistributed (along with replicas).
That’s it. It’s pretty much just moving a slider and waiting a few minutes.
Scaling Down
At some point, you may want to scale down your cluster. Perhaps you need 5 servers during a certain part of the year, but you only need 3 for other parts, and you’d like to save some money on your Azure bill.
Once again, this is just a matter of adjusting the slider. However, it’s a good idea to scale down one server at a time to avoid any risk of data loss.
When you scale down, Azure will pick a VM to decommission. Couchbase Server can respond in one of two ways:
-
Default behavior is to simply indicate that a node is down. This could trigger an email alert. It will show as 'down' in the UI.
-
Auto-failover can be turned on. This means that once a node is down, the Couchbase cluster will automatically consider it 'failed', promote the replicas on other nodes, and rebalance the cluster.
I’m going to leave auto-failover off and show the default behavior.
First, the server will show a status of 'deleting' in the Azure portal.
Soon after, Couchbase will recognize that a node is not responsive. It will suggest failover to 'activate available replicas'.
I’ll go ahead and do just that.
Once it’s removed from the cluster, you’ll need to trigger a 'rebalance'.
Summary and resources
Scaling a Couchbase cluster on Azure is simply a matter of using the slider.
If you’re scaling down, consider doing it one node at a time.
For more information, check out these resources:
-
A video by Ben Lackey covering the scaling process of Couchbase on Azure.
-
Make sure to read up on Auto-failover in the documentation.
-
Also check out the documentation on rebalancing.
If you have questions, please contact me on Twitter @mgroves or leave a comment.
