A piggy bank of commands, fixes, succinct reviews, some mini articles and technical opinions from a (mostly) Perl developer.

SSH tunnel using a jump host

Access a service on a remote machine via an intermediary

ssh -v -L 4444:app.example.com:5000 $USER@jump.example.com -nNT

Now you can access the service running on app.example.com:5000 by going to localhost:4444 in your browser.

Explanation of the command

  • from the host machine (where you are running the command)
  • connect to jump.example.com as user $USER
  • once there, access service app.example.com on port 5000
  • then make that service available on the host machine on port 4444

Advanced usage - Two jumps

ssh -J user@jump.example.com user@app.example.com -L 1111:database.example.com:3306 -nNT -vvv

Notes:
-J jumps to another host
-L makes a tunnel to a service that's already running

Now you can do:

mysql --protocol=tcp --host=127.0.0.1 --port=1111

Notes:
- you must specify protocol because of the tunnel
- specifying 127.0.0.1 (instead of "localhost") prevents MySQL trying to use a local socket and failing

Use a proxy on a remote machine via an intermediary

If there's a proxy you need to use: proxy.example.com:8888 -- but you can only access it from jump.example.com -- then set up a tunnel like this:

ssh -A -L 4444:proxy.example.com:8888 $USER@jump.example.com -nNTv

Now you can use http://localhost:4444 as your proxy server, instead of http://proxy.example.com:8888


Vim mappings for commands like JSON pretty-print and perltidy

imap :!python -m json.tool
map :%!perltidy

?
nmap :%!python -m json.tool

# AB
nmap :%!python -m json.tool
map :!perl -cW %
map :%!perltidy
map :resize +10

How to work with dist.ini as a user

You are a developer who needs to work with a Perl package. Instead of a cpanfile it has a dist.ini. Pop quiz, hot shot: What do you do?

Answer:

  • cpm install Dist::Zilla
  • dzil build
  • Follow instructions to install dependencies using cpanm
    • Or if you prefer cpm (and you should), then try this: dzil listdeps | xargs cpm install
  • Or: dzil authordeps --missing | xargs cpm install

Mac clients for AWS RedShift

JetBrains just released DataGrip ( JetBrains DataGrip: Your Swiss Army Knife for Databases and SQL ). I've been using it full time during the EAP (aka "beta"). It's expensive ($90/yr or $9/mth) but very, very good. Never loses your work. Starts up right where it left off. Dark and light themes. DB specific syntax highlighting. Good stuff.

Before that I was using Navicat Premium Essentials. I got it for ~$20. It's now ~$150 which is too much given it's limited functionality and spotty reliability IMO.
re:dash is overly simple for serious use IMO. Edit: We're actually using this now. It's more of service than an SQL client. It allows casual users (e.g. not full time analysts) to run queries without any setup. Great for centralised dashboards and sharing queries. Try this before you commit to something like Periscope, Looker or Mode.
Portico (was PG Commander) looks interesting but I (personally) need to work with multiple database flavors so it's a non-starter.
SQuirreL and SQL Workbench/J are both pretty crunchy and old feeling Java apps. If you've been on a Mac for a while they will make your eyes bleed.
There is also Toad on the Mac App Store. The screenshots look Mac native but it's pretty crunchy and unpleasant. It feels like going back in time.
Update 2016–09–02: I would suggest that you also look into the “code notebook” applications that are available now, e.g. JupyterZeppelin, and Beaker. You may well find these tools more useful unless you’re a full time DBA. We have replaced Re:Dash with Zeppelin internally.