<commit_before>-- a weather station
CREATE TABLE german_climate.stations (
  id string primary key,
  name string
) with (number_of_replicas=0, refresh_interval=0); -- settings for import purposes only

-- the location of a weather station which might have changed over time
CREATE TABLE german_climate.station_locations (
  station_id string,
  position geo_point,
  height int, -- height in m
  from_date timestamp, -- station has been at this location from this point in time (inclusive)
  to_date timestamp    -- station has been at this location up to that point in time (inclusive)
) clustered by (station_id)
with (number_of_replicas=0, refresh_interval=0); -- settings for import purposes only


-- the actual measurement
-- might not contain data for every possible column
CREATE TABLE german_climate.data (
  date timestamp,
  station_id string,
  temp float, -- temperature in °C
  humility double, -- relative humulity in percent
  cloudiness int,  -- 0 (cloudless)
                   -- 1 or less (nearly cloudless)
                   -- 2 (less cloudy)
                   -- 3
                   -- 4 (cloudy)
                   -- 5
                   -- 6 (more cloudy)
                   -- 7 or more (nearly overcast)
                   -- 8 (overcast)
                   -- -1 not availavle
  rainfall_fallen boolean, -- if some precipitation happened this hour
  rainfall_height double,  -- precipitation height in mm
  rainfall_form int, -- 0 - no precipiation
                     -- 1 - only "distinct" (german: "abgesetzte") precipitation
                     -- 2 - only liquid "distinct" precipitation
                     -- 3 - only solud "distinct" precipitation
                     -- 6 - liquid
                     -- 7 - solid
                     -- 8 - solid and liquid
                     -- 9 - no measurement
  air_pressure double,  -- air pressure (Pa)
  air_pressure_station_height double, -- air pressure at station height (Pa)
  ground_temp array(float), -- soil temperature in °C at 2cm, 5cm, 10cm, 20cm and 50cm depth
  sunshine_duration double, -- sum of sunshine duration in that hour in minutes<commit_msg>add primary key to normalized schema<commit_after>-- a weather station
CREATE TABLE german_climate.stations (
  id string primary key,
  name string
) with (number_of_replicas=0, refresh_interval=0); -- settings for import purposes only

-- the location of a weather station which might have changed over time
CREATE TABLE german_climate.station_locations (
  station_id string,
  position geo_point,
  height int, -- height in m
  from_date timestamp, -- station has been at this location from this point in time (inclusive)
  to_date timestamp    -- station has been at this location up to that point in time (inclusive)
) clustered by (station_id)
with (number_of_replicas=0, refresh_interval=0); -- settings for import purposes only


-- the actual measurement
-- might not contain data for every possible column
CREATE TABLE german_climate.data (
  date timestamp primary key,
  station_id string primary key,
  temp float, -- temperature in °C
  humility double, -- relative humulity in percent
  cloudiness int,  -- 0 (cloudless)
                   -- 1 or less (nearly cloudless)
                   -- 2 (less cloudy)
                   -- 3
                   -- 4 (cloudy)
                   -- 5
                   -- 6 (more cloudy)
                   -- 7 or more (nearly overcast)
                   -- 8 (overcast)
                   -- -1 not availavle
  rainfall_fallen boolean, -- if some precipitation happened this hour
  rainfall_height double,  -- precipitation height in mm
  rainfall_form int, -- 0 - no precipiation
                     -- 1 - only "distinct" (german: "abgesetzte") precipitation
                     -- 2 - only liquid "distinct" precipitation
                     -- 3 - only solud "distinct" precipitation
                     -- 6 - liquid
                     -- 7 - solid
                     -- 8 - solid and liquid
                     -- 9 - no measurement
  air_pressure double,  -- air pressure (Pa)
  air_pressure_station_height double, -- air pressure at station height (Pa)
  ground_temp array(float), -- soil temperature in °C at 2cm, 5cm, 10cm, 20cm and 50cm depth
  sunshine_duration double, -- sum of sunshine duration in that hour in minutes
{'fraction_non_alphanumeric': 0.07390406242827634, 'fraction_numerical': 0.013770943309616708, 'mean_word_length': 2.0055172413793105, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6788821', 'n_tokens_mistral': 1210, 'n_tokens_neox': 1095, 'n_words': 462}