Get The Last Date with a where clause (Matillion ETL)

So, if you have not tried this ELT (not ETL) then I would try it out www.matillion.com  @matillion , It is geared up for AWS Redshift but also has some other connectors in it. If you want to shift on premise data into Redshift then at the moment I have not found anything better in the price range or functionality.

I use Pentaho and their ETL tool so I am very familiar with ETL and for what this product was designed for kills the competition. As always this is not a sales pitch but a how to do reminder.

One of the first things that you will want to do is a staged dataload. So you want the last update from the Redshift table fed into you SQL query to get your on premise date.

One of the issue I had was that the date from Redshift was not compatible in anyway with the date format used by Oracle. You have therefore to turn to the Python Script component

One of the problems also is if you want to include a where clause. The language can be Python2. Python3 or Jython.  This is written in Jython.

 

You will then paste the following code into the script. Note I am not a Jython expert, but this runs pretty quick.

 

cursor = context.cursor()

#This is the argument that you want to pass to the SQL statement if you want multiple arguments then (‘x’,’y’,)  NOTE the , at the end

args = ('DOSB',)

# This is the SQL statement note that this uses the environmental variable SALES_HISTORY which is cool when you are testing different tables. I have a column called site_id in the table.

cursor.execute('select max(doc_date) from ${SALES_HISTORY} where site_id =?',args)

# This runs the SQL and assigns it to the Last_Date

Last_Date = cursor.fetchone()[0]

# We need to turn this into a string to manipulate it now

Last_Date_String = str(DOSB_Date)

# You can now substring it, it is a little strange in the format and this was a Google to get the syntax. You do not need the print statements for production, but useful when debugging

year = Last_Date_String[:4]
print year
month = Last_Date_String[5:5+2]
print month
day = Last_Date_String[8:8+2]
print day

 

# We now need to put it all together to create a new field to use

NewDate = day+month+year
print NewDate

# The last thing to do is assign this to a environmental variable. In this example the variable has already been created and is called LastDate

context.updateVariable('LastDate', NewDate)
print DOSB_LastDate

 

For completeness, to get this variable into a piece of SQL that Oracle understands you need to use the following :-

where history_date >= to_date('${LastDate}','MMDDYYYY')

 

The tool is great but there is a bit of a learning curve as with anything.

 

Good luck and as I learn more little cool features I will add them as always.

 

 

 


Securing a Apache Directory with .htaccess

So I quickly needed a secure directory to deliver some files.

It can not be that hard can it?

I already had a webserver so I just borrowed a little disk space and created my directory.

I know you could secure this with a .htaccess but it took me forever to find the small nugget on information to allow me to get it working.

 

The idea is that you have two files .htaccess and .htpasswd

The .htpasswd I stored in the root on my web server so I have to amend the /etc/httpd/conf/httpd.conf  file in the

<Directory “/var/www/html”> element of the configuration.

You need to change this  AllowOverride None to AllowOverride AuthConfig

While you are in this config you might as well create the virtual host setting.

<VirtualHost *:80>
ServerName download.acme.com
DocumentRoot /var/www/html/download

<Directory "/var/www/html/download">
 AllowOverride All
 allow from all
 Options +Indexes
 </Directory>
</VirtualHost>

Now create the .htaccess file

vi /var/www/html/download/.htaccess

And add the following

AuthType Basic
AuthName "restricted area"
AuthUserFile /var/www/html/.htpasswd
require valid-user

 

Now you need to create the .htpasswd file. The password had to be encrypted so you can not create this by hand

cd /var/www/html/

And issue this command

htpasswd -bc .htpasswd someuser somepass

You know have a protected directory

 


ResourceSpace – changing the search tile

resourcespace

 

I personally do not like the search tile like this, to remove the basic search functionality is fairly easy.

Admin >> System Settings >> User interface >> Basic Simple Search

Change to “Enable”

So you go from this to this

Screen Shot 2018-04-04 at 23.40.52Screen Shot 2018-04-04 at 23.48.03

 


ResourceSpace – changing the metadata

resourcespace

Ok now you have changed the look of the application it is now time to set some of the inner workings. One of these settings is changing keywords that is associated with your images, e.g. brand, pattern, product, type of image etc… the world is your oyster. Well to the tune of 6 key areas.

Screen Shot 2018-04-04 at 21.55.59

There are two lots of changes that you can do here.

Change the titles i.e. “Keywords – Emotion” and the actual entries that can be selected

So to change these

Admin >> System >> Manage metadata fields

Search for keywords

Screen Shot 2018-04-04 at 22.13.20

You can now change the name and the values that can be selected. The other very cool thing is that you can add new Titles and selections, you can therefore add as many as you like, this is pretty cool.

Be careful as lists appear as checkboxes in the search. If you choose though the Dynamic Keywords List this make this type ahead which is pretty cool and makes the search page more slick.

 


ResourceSpace – changing the logon screen

resourcespace

You want to obviously brand the login page to satisfy your marketing team.

Screen Shot 2018-04-04 at 19.11.42

To change “Welcome to ResourceSpace, please log in…”

Admin >> System >> Manage Content

Enter  welcomelogin in the name search, then just edit to your requirements

To stop people applying for an account “Click here to apply for an account”

Admin >> System >> System configuration >> User Accounts

Allow users to request accounts change to  – No

The change the top header

Screen Shot 2018-04-04 at 20.02.28

Logo change

Admin >> System >> System configuration >> User Interface

Screen Shot 2018-04-04 at 20.08.11

The last thing is the background colour and font colour.

You can just change the css file, not sure if this is the best practice. I would suspect not due to upgrade aspects.

PleaseNote READER BEWARE.

cd /var/www/html/resourcespace/css

cp colour.css colour_org.css

sed -e 's/color:#474747;background: #ebebeb/color:#474747;background: #07114f/' colour.css > colour2.css
sed -e 's/h1 {color: #000000;}/h1 {color: #ffffff;}/' colour.css > colour2.css

yes | cp colour2.css colour.css

rm -f colour2.css

 

 

 


ResourceSpace – managing the tiles on the dashboard

resourcespace

Now you have the ResourceSpace  DAM up and running you want to do stuff with it. No one has written a book, (maybe I should. It would be a pretty short book though 🙂 ) to tell you how to administer it, so with a bit from the Knowledge base, some from the Google groups https://groups.google.com/forum/#!forum/resourcespace and just general Google, which tends to get you to the group you can start you journey.

There are some odd things, but once you get into it, it is not so bad

 

So you want to tidy up the front screen.

I wanted to remove the “Knowledge Base Tile”, this took forever to find.

Select the tile and drag it to the left, you will see a Red Remove arrow appear, drop the tile over the arrow and you will be presented with a dialog box

Screen Shot 2018-04-04 at 18.41.34

You can “Delete tile for all users” or you can choose the “Manage all user tiles” The url for this is http://images.acem.com/pages/team/team_dash_tile.php, this can be seen in the Admin section of “Manage dash tiles”

So for people searching Google. “How do I remove a dashboard tile from Resourcespace”, hopefully you will find this.

 

 

 

 


Resource Space – error creating a user

resourcespace

One of the first things I did was to create a user, just to test the system and received this error

/var/www/html/resourcespace/include/message_functions.php line 283: count(): Parameter must be an array or an object that implements Countable

It created the user but what a pain, this is to do with php 7.2 and version 8.4 of ResourceSpace. Have a look below (ResourceSpace errors) for the work around for now.

 

ResourceSpace errors

/var/www/html/resourcespace/include/message_functions.php line 283: count(): Parameter must be an array or an object that implements Countable

vi /var/www/html/resourcespace/include/message_functions.php line 283

:set number

:283

Copy the line, comment it and then change it to (this is not my code, I am no PHP developer)

// if($remote_activity==0 || $remote_refs==0 || count($remote_refs)==0 ){return false;}
if($remote_activity==0 || $remote_refs==0 || ((is_array($remote_refs)) && (count($remote_refs)==0 ))){return false;}

 

This line may be needed more often to get around some of the issues

((is_array($remote_refs)) && (count($remote_refs)==0 ))