Question: I have an assignment for class and I'm not quite sure how to approach this.

I currently have...

```
SELECT payTypeCode, '$' + CONVERT(VARCHAR(12), CAST(AVG(payAmount) AS MONEY), 1) AS 'average supervisor salary'
    FROM employee
    GROUP BY employee.payTypeCode
    HAVING COUNT(employee.payTypeCode) >= 5

```

But it says it only wants me to list the average salary if at least five supervisors have that specific pay type code. I tried to think of using COUNT in a substring but I seem to be hitting a mental wall. Any thoughts?

Thanks for the help.
Bryan

Answer: 
One way to do it is to download a file locally (using `hdfs dfs -get` command) and then follow [the procedure](https://stackoverflow.com/a/19127748/3937850) for detecting compression format for local files. 

This should work quite well for files compressed **outside** of Hadoop. For files generated within Hadoop, this will work only for limited number of cases, e.g. text files compressed with Gzip.

Files compressed within Hadoop are likely to be so called "container formats", e.g. [Avro](https://avro.apache.org/docs/1.6.3/spec.html#Object+Container+Files), Sequence Files, Parquet, etc. That means that not the entire file is compressed, but only chunks of data inside the file. The hive's `describe formatted table` command that you're mentioning can indeed help you to figure out the input format of underlying files.

Once you know the file format, you should refer to the documentation/source code of the file format for the reference on codec detection. Some file formats even come with command line tools to look into the file's metadata which reveals the compression codec. Some examples:

[Avro](https://mvnrepository.com/artifact/org.apache.avro/avro-tools/1.7.4):

```
hadoop jar /path/to/avro-tools.jar getmeta FILE_LOCATION_ON_HDFS --key 'avro.codec'

```

[Parquet](https://mvnrepository.com/artifact/org.apache.parquet/parquet-tools/1.8.1)

```
hadoop jar /path/to/parquet-tools.jar meta FILE_LOCATION_ON_HDFS

```
If you are asking what codec is being used by mapreduce for intermediate map output and/or final output you can check Hadoop's configuration file, typically located at `<HADOOP_HOME>/etc/mapred-site.xml`. I am not, however, aware of a way to check directly from the command line.

Settings for intermediate map output compression should look something like below:

```
<property>
<name>mapreduce.map.output.compress</name>
<value>true</value>
</property>

<property>
<name>mapreduce.map.output.compress.codec</name>
<value>org.apache.hadoop.io.compress.GzipCodec</value>
</property>

```

Settings for job output compression should look something like below:

```
<property>
<name>mapreduce.output.fileoutputformat.compress</name>
<value>true</value>
</property>

<property>
<name>mapreduce.output.fileoutputformat.compress.type</name>
<value>BLOCK</value>
</property>

<property>
<name>mapreduce.output.fileoutputformat.compress.codec</name>
<value>org.apache.hadoop.io.compress.GzipCodec</value>
</property>

```

From those two snippets, you can see that I'm using the GZIP codec, and that I'm compressing both the intermediate map output as well as the final output. Hope that helps!