Skip to content

Instantly share code, notes, and snippets.

View timrobertson100's full-sized avatar

Tim Robertson timrobertson100

57 followers · 7 following
View GitHub Profile
@timrobertson100
timrobertson100 / uk1.sql
Created May 8, 2017 17:14
publisherKey and datasetKey
select
p.key as publisherKey, p.title, d.key as datasetKey, d.title, replace(replace(e.url, 'http://registry.als.scot/GBIF/', ''), '.zip', '') as guid
from
dataset d JOIN
dataset_endpoint de ON d.key=de.dataset_key JOIN
endpoint e ON e.key=de.endpoint_key JOIN
organization p ON d.publishing_organization_key = p.key
where
e.url like 'http://registry.als.scot/GBIF/%'
order by p.title, d.title
@timrobertson100
timrobertson100 / uk1.sql
Created May 8, 2017 17:14
publisherKey and datasetKey
select
p.key as publisherKey, p.title, d.key as datasetKey, d.title, replace(replace(e.url, 'http://registry.als.scot/GBIF/', ''), '.zip', '') as guid
from
dataset d JOIN
dataset_endpoint de ON d.key=de.dataset_key JOIN
endpoint e ON e.key=de.endpoint_key JOIN
organization p ON d.publishing_organization_key = p.key
where
e.url like 'http://registry.als.scot/GBIF/%'
order by p.title, d.title
@timrobertson100
timrobertson100 / export.sql
Created April 19, 2017 20:23
Export for JW
SET hive.exec.compress.output = true;
SET avro.output.codec = gzip;
CREATE TABLE tim.jw1 STORED AS avro AS
SELECT * FROM prod_a.occurrence_hdfs
WHERE
dynamicProperties is not null or
occurrenceRemarks is not null or
organismRemarks is not null or
fieldNotes is not null
@timrobertson100
timrobertson100 / example.txt
Created April 3, 2017 13:42
Broken Contentful post
- asset publishing: http://www.gbif.org/sites/default/files/gbif_project/files/Encounter_Bay.pdf
(node:90697) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 2): ServerError: {
"request": {
"url": "https://api.contentful.com:443/spaces/210sv5srxtrc/entries/82202",
"headers": {
"Accept": "application/json, text/plain, */*",
"Content-Type": "application/vnd.contentful.management.v1+json",
"X-Contentful-User-Agent": "contentful-management.js/1.3.1",
"Authorization": "Bearer 5760fa26420af97aa3a38bddeb96688a6a29b3d29c555ad881224a0a2c617943",
"user-agent": "node.js/v7.7.1",
@timrobertson100
timrobertson100 / example.json
Created March 30, 2017 18:59
Asset
let asset = await space.createAsset({
fields: {
title: {'en-GB': "test"},
description: {'en-GB': "test"},
file: {
'en-GB': {
contentType: "image/png",
fileName: "logo-1.png",
upload: "https://api.gbif.org/v1/image/unsafe/cms.gbif-uat.org/sites/default/files/gbif_event/images/logo-1.png"
}
@timrobertson100
timrobertson100 / Example.json
Created March 29, 2017 11:57
Bust occurrences
{
"datasetUuid":"15f819bd-6612-4447-854b-14d12ee1022d",
"status":"UPDATED",
"crawlAttempt":24,
"oldOccurrence":{
"key":1139047634,
"datasetKey":"15f819bd-6612-4447-854b-14d12ee1022d",
"publishingOrgKey":null,
"publishingCountry":null,
"protocol":"DWC_ARCHIVE",
@timrobertson100
timrobertson100 / events.sql
Created March 28, 2017 18:30
Extracting events from Drupal
SELECT
n.nid AS id,
n.title AS title,
b.body_value AS summary,
concat(
DATE_FORMAT(t.ge_date_ical_value, '%Y-%m-%dT%H:%i'),
CASE WHEN t.ge_date_ical_offset <0 THEN '' ELSE '+' END,
TIME_FORMAT(SEC_TO_TIME(COALESCE(t.ge_date_ical_offset, 0)),'%H:%i')) AS start,
concat(
DATE_FORMAT(t.ge_date_ical_value2, '%Y-%m-%dT%H:%i'),
@timrobertson100
timrobertson100 / Example-message.json
Created March 26, 2017 07:59
{
"datasetUuid":"4fa7b334-ce0d-4e88-aaae-2e0c138d049e",
"status":"UPDATED",
"crawlAttempt":7,
"oldOccurrence":{
"key":1161897615,
"datasetKey":"4fa7b334-ce0d-4e88-aaae-2e0c138d049e",
"publishingOrgKey":"e2e717bf-551a-4917-bdc9-4fa0f342c530",
"publishingCountry":"US",
"protocol":"DWC_ARCHIVE",
@timrobertson100
timrobertson100 / gist:f92d34cf7b8c857f82638a9f74a2a298
Created March 23, 2017 14:30
Protista|Chlorophyta||Chlorococcales|Characiaceae|Characium|Characium minutum var. disculiferum|minutum|disculiferum|variety
||||Algues|Ectocarpus|Ectocarpus littoralis|||
||||Algues|Ectocarpus|Ectocarpus littoralis|||
||||Algues|Ectocarpus|Ectocarpus littoralis|||
||||Algues|Ectocarpus|Ectocarpus littoralis|||
||||Algues|Hildenbrandtia|Hildenbrandtia rosea Kütz.|||
||||Algues|Hildenbrandtia|Hildenbrandtia rosea Kütz.|||
||||Algues|Hildenbrandtia|Hildenbrandtia rosea Kütz.|||
||||Algues|Hildenbrandtia|Hildenbrandtia rosea Kütz.|||
||||Algues|Ulva|Ulva plantaginea var.|||
@timrobertson100
timrobertson100 / drop.sql
Created March 9, 2017 13:07
drops
DROP TABLE prod_b.0023084_160910150852091_interpreted;
DROP TABLE prod_b.0023084_160910150852091_verbatim;
DROP TABLE prod_b.0035467_160910150852091;
DROP TABLE prod_b.0035467_160910150852091_citation;
DROP TABLE prod_b.0035499_160910150852091;
DROP TABLE prod_b.0035499_160910150852091_citation;
DROP TABLE prod_b.0040768_160910150852091_interpreted;
DROP TABLE prod_b.0040768_160910150852091_verbatim;
DROP TABLE prod_b.0040789_160910150852091_interpreted;
DROP TABLE prod_b.0040789_160910150852091_verbatim;