scala program to create a date time dimension CSV file for a data mart or data warehouse

date dimension.md

You may need from time to time to grab a day-level granularity date dimension. You can find some wizards in some database packages or some PL/SQL commands to generate it, but if you just need something simple, try the below:

package datedim

import collection.JavaConverters._
import au.com.bytecode.opencsv.CSVReader
import java.io.FileReader
import scala.collection.mutable.ListBuffer
import au.com.bytecode.opencsv.CSVWriter
import java.io.FileWriter
import java.nio.file.Files
import java.nio.file.Paths
import java.io.PrintWriter
import com.beust.jcommander.Parameter
import com.beust.jcommander.JCommander
import java.io.InputStreamReader
import java.io.OutputStreamWriter
import java.text.ParseException
import com.beust.jcommander.IParameterValidator
import com.beust.jcommander.ParameterException
import util.control.Exception._
import com.beust.jcommander.IStringConverter
import org.threeten.bp.DateTimeException
import org.threeten.bp.LocalDate
import org.threeten.bp.ZoneId
import org.threeten.bp.Period
import org.threeten.bp.format.DateTimeFormatter
import org.threeten.bp.format.DateTimeFormatterBuilder
import org.threeten.bp.temporal.IsoFields

object DateDim {

  class DateOK extends IParameterValidator {
    def validate(name: String, value: String): Unit = {
      handling(classOf[DateTimeException]) by { t =>
        throw new ParameterException("Invalid date format or value for option " + name
          + ": [" + value + "]: " + t.getMessage)
      } apply LocalDate.parse(value)
    }
  }

  class DateParser extends IStringConverter[Option[LocalDate]] {
    def convert(value: String): Option[LocalDate] = Some(LocalDate.parse(value))
  }

  object CommandLine {
    @Parameter(names = Array("-o", "--output"),
      description = "Output file. Default is stdout.")
    var output = "stdout"

    @Parameter(names = Array("-h", "--help"),
      description = "Help", help = true)
    var help = false

    @Parameter(names = Array("-s", "--start"),
      description = "Start date. Default is today. ISO local date format: 2014-01-01.",
      validateWith = classOf[DateOK],
      converter = classOf[DateParser])
    var startDateTime: Option[LocalDate] = None

    @Parameter(names = Array("-e", "--end"),
      description = "End date. Default is start + 365 days. ISO format.",
      validateWith = classOf[DateOK],
      converter = classOf[DateParser])
    var endDateTime: Option[LocalDate] = None

    @Parameter(names = Array("-z", "--zone"),
      description = "time zone id. Default is the system time zone. NOT HONORED!")
    var tz = ZoneId.systemDefault

    @Parameter(names = Array("-d", "--delimiter"), description = "Output delimiter")
    var delimiter = ","
  }

  def main(args: Array[String]): Unit = {

    handling(classOf[ParameterException]) by { t =>
      System.err.println("Parameter error: " + t.getMessage)
      System.err.println("Use -h to print help")
      sys.exit(-1)
    } apply {
      val jc = new JCommander(CommandLine, args: _*)
      if (CommandLine.help) {
        jc.usage()
        sys.exit(0)
      }
    }

    if (CommandLine.startDateTime == None)
      CommandLine.startDateTime = Some(LocalDate.now())
    if (CommandLine.endDateTime == None) {
      CommandLine.endDateTime =
        Some(CommandLine.startDateTime.get.plus(Period.ofYears(1)))
    }

    var start = CommandLine.startDateTime.get
    val end = CommandLine.endDateTime.get
    var separator = CommandLine.delimiter
    val tz = CommandLine.tz

    val header = Array("datepk", "dateformatted",
      "day", "month", "year", "calquarter",
      "dayofweek", "dayofyear", "weekofyear",
      "dayname", "monthname", "workdayflag")

    val pwriter = new PrintWriter(if (CommandLine.output == "stdout")
      new OutputStreamWriter(System.out)
    else
      new FileWriter(CommandLine.output), true)

    val daypkFormatter = DateTimeFormatter.BASIC_ISO_DATE
    val dayFormatter = DateTimeFormatter.ISO_LOCAL_DATE
    val weekOfYearFormatter = new DateTimeFormatterBuilder()
      .appendValue(IsoFields.WEEK_OF_WEEK_BASED_YEAR, 2).toFormatter

    def out(text: Any, sep: Boolean = true): Unit = {
      pwriter.print(text.toString)
      if (sep) pwriter.print(separator)
    }

    var counter = 0
    pwriter.println(header.mkString(separator))
    while (start.compareTo(end) <= 0) {
      out(daypkFormatter.format(start))
      out(dayFormatter.format(start))

      val day = start.getDayOfMonth
      val month = start.getMonthValue
      val year = start.getYear

      out(day)
      out(month)
      out(year)

      month match {
        case q1 if (month <= 3) => out(1)
        case q2 if (month > 3 && month < 6) => out(2)
        case q3 if (month > 5 && month < 10) => out(3)
        case _ => out(4)
      }

      val dayOfWeek = start.getDayOfWeek
      val dayOfYear = start.getDayOfYear
      val weekOfYear = weekOfYearFormatter.format(start)

      out(dayOfWeek.getValue)
      out(dayOfYear)
      out(weekOfYear)

      val dayName = start.getDayOfWeek
      out(dayName)
      val monthName = start.getMonth
      out(monthName)

      dayOfWeek.getValue match {
        case d if d > 1 && d < 7 => out(1, false)
        case _ =>
      }

      pwriter.println()
      start = start.plus(Period.ofDays(1))
    }
    pwriter.close
  }

}

Here's my build.gradle file:

//
// date dimension generator
//
apply plugin: "maven"
apply plugin: "eclipse"
apply plugin: "scala"
apply plugin: "application"

mainClassName="datedim.DateDim"

ext.sourceCompatibility = JavaVersion.VERSION_1_7
ext.targetCompatibility = JavaVersion.VERSION_1_7

ext {
	scalaVersion='2.10.4-RC3'
	slf4jVersion="1.6.4"
	logbackVersion="1.0.1"
}

repositories() {
	mavenCentral()
	mavenLocal()
	maven { url 'http://source.mysema.com/maven2/releases' }
	flatDir { dirs "locallib" }
	ivy { url "${System.properties['user.home']}/.ivy2/local" }
	maven { url 'http://m2.neo4j.org' }
}


dependencies {

	compile 'org.threeten:threetenbp:0.8.1'
	runtime "ch.qos.logback:logback-classic:$logbackVersion"
	runtime "ch.qos.logback:logback-core:$logbackVersion"
	runtime "org.slf4j:jcl-over-slf4j:$slf4jVersion"
	runtime "org.slf4j:log4j-over-slf4j:$slf4jVersion"
	compile 'com.typesafe:scalalogging-slf4j_2.10:1.0.1'

	compile 'de.jollyday:jollyday:0.4.7'
	compile 'org.scalaz:scalaz-concurrent_2.10:7.1.0-M4'
	compile 'org.scalaz:scalaz-core_2.10:7.1.0-M4'

	compile "com.typesafe.slick:slick_2.10:2.0.0-RC1"
	compile "com.typesafe.akka:akka-actor_2.10:2.2.3"
	compile "com.jolbox:bonecp:0.8.0.RELEASE"

	compile "com.h2database:h2:1.3.174"

	compile "org.scala-lang:scala-library:${scalaVersion}"

	compile "com.beust:jcommander:1.35"

	compile 'net.sf.opencsv:opencsv:2.3'
	compile 'com.jsuereth:scala-arm_2.10:1.3'
}

test {
	testLogging.showStandardStreams = true
	useTestNG()
}

configurations  {
	all*.exclude module: 'commons-logging'
	all*.exclude module: 'log4j'
	all*.exclude module: 'jsr166y'
	all*.exclude module: 'scala-actors'
}

There is another one in java here.