Posts tagged ‘voila’

22 August, 2016

Easy clock dimension table

by gorthx

We’re currently redesigning our data warehouse, and we’re experimenting with a clock (or time, or time-of-day) dimension to represent the time of day, separate from the date dimension we already use. This table should contain a record for each minute of a day, allowing us to easily determine business hours in various timezones, etc etc.

I’m not entirely sure it’s going to work for us just yet; we have a lot of timestamp data, and seems like it’s more trouble than it’s worth1. But it never hurts to proof-of-concept something, right? (Except for all those times the proof-of-concept ends up being the production system…)

Anyway, somebody commented that “you could have the analyst whip up the dataset in just a few hours” … :sideeye: or, I could do it myself in Postgres in just a few minutes:

CREATE TABLE clock_dimension (
 id serial primary key
 , full_timestamp time
 , hour_of_day_24 integer
 , hour_of_day_12 integer
 , am_pm text
 , minute_of_hour integer
 , business_hours_weekday boolean
 , business_hours_weekend boolean

-- fill it with values
INSERT INTO clock_dimension (full_timestamp) VALUES (
generate_series('2016-08-05 00:00'::timestamp, '2016-08-05 23:59'::timestamp, '1 minute')::time
UPDATE clock_dimension SET
hour_of_day_24 = date_part('hour', full_timestamp)
, hour_of_day_12 = CASE
WHEN date_part('hour', full_timestamp) > 12
THEN date_part('hour', full_timestamp) - 12
WHEN date_part('hour', full_timestamp) = 0
ELSE date_part('hour', full_timestamp)
, am_pm = CASE
WHEN date_part('hour', full_timestamp) >=12
THEN 'pm'
ELSE 'am'
, minute_of_hour = date_part('minute', full_timestamp)
, business_hours_weekday = CASE
WHEN full_timestamp BETWEEN '08:00' AND '21:00'
THEN 't'::boolean
ELSE 'f'::boolean
, business_hours_weekend = 'f'

Sample data:

 id  | full_timestamp | hour_of_day_24 | hour_of_day_12 | am_pm | minute_of_hour | business_hours_weekday | business_hours_weekend 
 901 | 15:00:00       |             15 |              3 | pm    |              0 | t                      | f
 902 | 15:01:00       |             15 |              3 | pm    |              1 | t                      | f
 903 | 15:02:00       |             15 |              3 | pm    |              2 | t                      | f
 904 | 15:03:00       |             15 |              3 | pm    |              3 | t                      | f
 905 | 15:04:00       |             15 |              3 | pm    |              4 | t                      | f
 906 | 15:05:00       |             15 |              3 | pm    |              5 | t                      | f
 907 | 15:06:00       |             15 |              3 | pm    |              6 | t                      | f
 908 | 15:07:00       |             15 |              3 | pm    |              7 | t                      | f
 909 | 15:08:00       |             15 |              3 | pm    |              8 | t                      | f
 910 | 15:09:00       |             15 |              3 | pm    |              9 | t                      | f
 911 | 15:10:00       |             15 |              3 | pm    |             10 | t                      | f
 912 | 15:11:00       |             15 |              3 | pm    |             11 | t                      | f
 913 | 15:12:00       |             15 |              3 | pm    |             12 | t                      | f
 914 | 15:13:00       |             15 |              3 | pm    |             13 | t                      | f
 915 | 15:14:00       |             15 |              3 | pm    |             14 | t                      | f
 916 | 15:15:00       |             15 |              3 | pm    |             15 | t                      | f

1 – see also

1 August, 2016

Upgrade Ubuntu 15.04 to 16.04

by gorthx

I use Ubuntu on my home machines, and tend to rely on the GUI Software Updater for upgrades & such.  Life happens and I left one of them on 15.04 past EOL, and the Software Updater didn’t provide an upgrade path directly to 16.04.  I had to upgrade to 15.10 first, which is also EOL.

Here’s what I pieced together from various forums around the net.  (I took this after-the-fact from my command-line history;  I’ll take better notes next time.)

apt-get update
apt-get upgrade # make sure system is as up-to-date as possible
apt-get autoremove # clean up some leftover crap

apt-get dist-upgrade # recommended next step on one of the forums,
                     # but didn't actually do anything
                     # and I probably could have skipped this

do-release-upgrade -d # supposely this should have worked without the -d
                      # but it didn't
                      # -d means "latest development release"

And booyah, I’m on 15.10 and could continue on to 16.04.


30 March, 2016

This month’s RDS tip, a pgloader exercise, and bonus recruiter spam

by gorthx

1. Several months back, AWS re-worked the web console. You can get sparkline-style graphs for CPU usage, memory, storage, and database connections at the top level, instead of having to drill down to the Cloudwatch metrics for the instance.

I find this really handy – but for one quirk with the graphs:

There’s white space to the right of the red line. Therefore, I was interpreting the red line to mean “trouble is brewing & I should do something about this”.

Turns out that red line is the limit; there’s nowhere else to go. Whoops!

2. This week I finally had a reason to try out pgloader: One of my analysts needed some help loading some ugly fixed-width data1.

Installing was super-easy on my mac (`brew install pgloader`).  I worked through the examples before starting to work with my actual dataset2.

The data to be loaded came bundled as several text files: the data, plus three or four additional files describing the layout.  I wrote a truly glorious string of cut, sed, paste, and awk to create a pgloader control file that would work. And it did!


field1 | field2 | field3 
 [null] | D | IMITRI
 [null] | G | ABRIELLE
 [null] | M | ARK
 [null] | S | ELENA

Uh, what?

The data descriptions had character counts starting at 1, and pgloader expects them to start at 0 (as they should).  (For extra fun, the first column in all records of this dataset was nothing but spaces.)

3. This week’s hilarious recruiter spam:
“Hey there Gabrielle! I was doing my homework on sites like Meetup, GitHub, etc. and I noticed your Java skills.”

I’m pretty sure you didn’t.

1 – Is that redundant?
2 – 600 columns of fixed-width data?  Who does that?!
3 – Why couldn’t it all be in one file? Again: who does this?!4
4 – People who hate DBAs, that’s who.

29 November, 2013

OSX, VMWare, CentOS, and postfix

by gorthx

(Original title: “mail server arrrgh”)

I’m running VMWare Fusion on my Mac. I often run multiple VMs for testing Postgres on various OSes, and decided it would be fun if I could get system emails from the guests on the mac host. Yeah, “it would be fun”. Even though I was working with Postfix, this task gave me flashbacks to my sendmail experiences many years ago.

This is what I had to do to get it working. This isn’t in the order I did all the steps; there was a ton of trial, error, and wtfery that went on here. Also, this is on a machine that’s behind a few firewalls; probably not something you want to configure on a server that actually has a port open on the Internet. Although it seems that most of the mailserver-related footguns are no longer enabled by default. Caveat Emptor, or something.

Host: Mac OSX Lion 10.7.5
Guests: CentOS 6.4
VMWare: Fusion 4.1.3

Set up postfix on the mac host; starting with the instructions here:

This “worked” in that the simple “telnet to port 25” test worked (see “Useful tools and commands” below), but I had to make some additional changes to receive mail from my guest OSes.

On the Mac host, I made some changes to /etc/postfix/
myhostname = princess
mydomain = localdomain
mydestination = $myhostname, $myhostname.$mydomain, localhost.$mydomain, localhost, mailhost.$mydomain
inet_interfaces =,
mynetworks =,

The value for inet_interfaces is the IP of my vmware interface, obtained from ifconfig. You want the vmware8 interface and it should be a 192.168 address. I just added the whole vmware subnet to mynetworks parameter. You can read more about these parameters in; it is surprisingly well-commented.

I also commented out imap_submit_cred_file line as discussed here I’m not going to bother with this for local use.

‘postfix reload’ did not apply these changes for me; I had to explicitly stop and restart it1:
postfix stop
postfix start

On the guest:
First, I added the vmware host IP to /etc/hosts: princess princess.localdomain mailhost mailhost.localdomain

This worked via the ‘telnet to port 25’ test method, but regular email wasn’t getting delivered; in fact, it was bouncing. Looking into the mail queue, I discovered it was trying to use a completely different IP for its mailhost.

I checked out the ‘hosts’ value in /etc/nsswitch.conf (just grep hosts /etc/nsswitch.conf) and determined that it was set to ‘files dns’, which ordinarily indicates that /etc/hosts should override dns from the vmware dns server. That wasn’t what was happening. A bit of investigation revealed that postfix was already running2, so I had to dink with it on the guests as well.

Fixed by editing /etc/postfix/ (on the guest) to include this:
relayhost = mailhost.localdomain


Thanks to mjm for keeping me sane during this.

Useful tools/commands:
‘postfix status’ # see what postfix thinks it’s doing and get its pid, since the processes aren’t named postfix and therefor ‘ps -ef | grep post’ won’t show them :)

netstat -an | grep ‘\.25 ‘ # see if anything’s listening on port 25:
(master *) :::-->netstat -an | grep '\.25 '
tcp4 0 0 *.* LISTEN
tcp4 0 0 *.* LISTEN

mailq # see status of queued mail messages

[tail|vi|whatever] /var/log/mail.log # see connections, postfix startup/reload, etc

SMTP test program, something like this

You may want this as well:

1 – I’ve since been told it’s better to use launchctl to control processes.
2 – This surprised me, given the other things that are locked down/not available on the CentOS default.

5 April, 2013

Installing PostgreSQL (and friends) from source on Ubuntu 12.04 (Precise Pangolin)

by gorthx

This is an update to this post from a few years back. This time I’m installing Postgres 9.2.3, PostGIS 2.0.1, and pgAdmin 1.17.0 on Ubuntu 12.04.

read more »

3 August, 2012

First Foray into R

by gorthx

I was talking with [name redacted] about a side project the other day and he said “OMG you’re still using *gnuplot*?” So I figured I’d better get with the program and learn some R.

Luckily for me, Portland has an R users’ group, and they held a hackathon/workshop last week, newbies welcome. They’re a good group of people & I heartily recommend the workshop night. Special thanks to Homer for the personalized help and suggestions.

read more »

Tags: , ,
16 December, 2011

Generic steps for troubleshooting wireless on Ubuntu.

by gorthx

Fresh from my success with my Thinkpad T420, I tackled my Dell Latitude E6410, which had its own interesting quirks. So, here are some basic wireless troubleshooting steps for Ubuntu.

Step 1: Make sure the hardware switch is not set to off.

Step 1a: Is there another “hardware” switch? My HP2501p had an extra firmware switch for the wireless, accessible only from Windows. (Good thing I hadn’t deleted that partition…)

Step 2: Check the permissions: System -> Administration -> Users and Groups -> Advanced Settings; make sure “allow to connect to ethernet and wireless networks” is checked.

Step 3: Use lpsci to make sure your machine can see your card. Should look something like this (output filtered for brevity):
lspci -nn
02:00.0 Network controller [0280]: Broadcom Corporation BCM43224 802.11a/b/g/n [14e4:4353] (rev 01)

Step 4: Check the drivers: System -> Administration -> Additional Drivers. You should see a driver appropriate for your card there, e.g. I have the Broadcom STA Wireless Driver. It should show green and say “activated”. If not, click the “Activate” button. (I needed to reboot the Dell in order to get this change to take.)

Step 5: Find your ethernet interface:
lo no wireless extensions.

eth0 no wireless extensions.

eth1 IEEE 802.11 Access Point: Not-Associated
Link Quality:5 Signal level:0 Noise level:163
Rx invalid nwid:0 invalid crypt:0 invalid misc:0

…and enable power*:
sudo iwconfig eth1 txpower on

Et voila.

* This page: helped me figure this out.

2 December, 2011

Installing Ubuntu 10.10 on a Thinkpad 420

by gorthx

(This post is mainly about getting the RealTek wireless card working.)

I went with 10.10, mainly because I had the image handy on a USB key, and I’m not so excited about what I’ve heard about 11 yet. (Although I do intend to try it at my next available opportunity.)

To get the Thinkpad to boot from a USB, I hit F12 during boot (gotta be quick with it!) to access the boot menu, then -s to get the startup menu. (The ‘thinkvantage’ button didn’t get me where I wanted to be.) Once I was in the startup menu, I was in the ‘boot options’ tab. Hit the down arrow to select “USB HD”, then hit enter. Voila.

The install went pretty fast, but then I spent a fair bit of time with updates. In retrospect, I probably should have updated the image on the USB key. :shrug:

First, the most important configuration change: put the #&@* minimize/maximize buttons back on the right side, where they belong.

Next: install my favorite font.

Everything worked out of the box (external keyboard, external monitor, card reader, etc) except wireless. My laptop wasn’t even detecting that I had a wireless interface.

First I tried enabling “connect to ethernet and wireless networks”. (System -> Administration -> Users & Groups; select the user; click “Advanced Settings”; select the “User Priveleges” tab; make sure “Connect to wireless and ethernet networks” is checked). No dice.

I could see my card:
lspci -nn
Realtek Semiconductor Co., Ltd. Device [10ec:8176] (rev 01)

…but I needed the drivers. This thread (specifically, the post by canucked) had the info I needed:

sudo add-apt-repository ppa:lexical/hwe-wireless
sudo apt-get update
sudo apt-get install rtl8192ce-dkms

(Kind of confusing that the driver has what seems to be a different model # in it, but there it is.)

I pulled up System -> Administration -> Additional Drivers to check the status of my new driver, and discovered it was activated but not currently in use. Deactivating and reactivating it didn’t change anything, but a reboot did.

4 October, 2011

This week’s find: CREATE OR REPLACE VIEW

by gorthx

How many times am I working with Postgres and say “I wish I could…” and I can? Lots. Here’s another one: CREATE OR REPLACE VIEW. As long as you’re not removing columns, or changing the datatype or name of the existing columns, you can use this. It’s great because it preserves rules, triggers, and perms, saving you from digging them out of your pg_dump and re-applying them. (Schema changes, whee.)

As of 8.4, thanks to Robert Haas, you can even add columns to the view (as long as they’re at the end):

— original view
testy=# CREATE VIEW view_cisco_routers AS
SELECT device_name
FROM devices
WHERE device_type = ‘Router’;

— then the specs change, and we want to include the IP address, too
testy=# CREATE OR REPLACE VIEW view_cisco_routers AS
SELECT device_name, device_ip
FROM devices
WHERE device_type = ‘Router’;

Usually I want to alter or remove columns, so CREATE OR REPLACE VIEW has a limited use case for me. However, I recently changed the datatype of a column* that I used in the WHERE clause for about 20 views, and CREATE OR REPLACE VIEW was definitely simpler (and therefore less error-prone) than dropping & re-creating them all.

* to ENUM, which I’ll write about in another week or so.

Tags: ,
2 September, 2011

Updating multiple rows using a FROM clause

by gorthx

Last week I discovered a new-to-me Postgres feature, which has been around for quite a while: you can use a FROM clause to UPDATE multiple rows based on values in another table. (See docs here and here. The second link is older but has a more illustrative example.)

After some recent large-scale changes to our network infrastructure, we had to update our inventory database to reflect which equipment had been upgraded. In the past, I’ve accomplished this by using perl to generate a series of individual statements like
UPDATE equipment
SET hostname = 'shiny', model = 'FancyCiscoRouter'
WHERE hostname = 'crufty';

into a file, and just running that with i. The not-so-new way is faster and easier.

I had my main table of equipment names:
foo=# SELECT * from equipment;
ip | hostname | model
----------+--------------------+------- | old-and-in-the-way | 12000 | crusty | 7300 | decrepit | 7300

And a new table with the new equipment names & models:
foo=# SELECT * from new_equipment ;
ip | hostname | model
----------+----------+------- | unicorn | ASR | rainbow | 2900 | glitter | 2900

Here’s how you’d update the main table from the information in the new table:
UPDATE equipment
SET hostname=new_equipment.hostname, model = new_equipment.model
FROM new_equipment
WHERE equipment.ip = new_equipment.ip;

foo=# SELECT * FROM equipment ;
ip | hostname | model
----------+----------+------- | unicorn | ASR | rainbow | 2900 | glitter | 2900

…don’t forget to COMMIT. :)

Is anybody besides me super-annoyed that Cisco gave a router platform the same number series as the old Catalyst switches? Talk about confusing. “2900s? I thought we replaced those?”

Tags: ,